Help formatting query to SQL-92 standard

[TheBugSlayer]

All,

A third-party vendor has submitted a script for execution to resolve an issue users are experiencing. Since this is serious business and the query is not in standard format, I want to make sure I understand its purpose correctly. The query is:

delete GL20000 from GL00100, GL20000 where GL20000.ACTINDX = 1234 and GL00100.ACCTTYPE = 9 and GL20000.SOURCDOC = 'XYZ'

When I substitute DELETE with SELECT, the query returns 5049 rows...That's a lot of rows to delete by accident!

Since there is no equation between both tables in the WHERE clause, I am not sure what's happening here. Can someone help me explain the query or write it in SQL-92 format?

Thank you!


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[bborissov]

There is NO join between the tables, so I think this is wrong query.
It should be something like this:

delete GL20000
from GL00100
INNER JOIN GL20000 ON  GL20000.???????? =  GL00100.?????????
where GL20000.ACTINDX = 1234
  and GL00100.ACCTTYPE = 9
  and GL20000.SOURCDOC = 'XYZ'

or in SQL92:

delete GL20000 
from GL00100, GL20000 
where GL20000.???????? =  GL00100.?????????
AND GL20000.ACTINDX = 1234 
and GL00100.ACCTTYPE = 9
and GL20000.SOURCDOC = 'XYZ'

Borislav Borissov
VFP9 SP2, SQL Server

 

[TheBugSlayer]

Thanks Borislav. Yep...It's missing that GL20000.???????? = GL00100.?????????
part.

I think you mixed up the statements. The SQL-92-compliant one should be the one above, which implements JOIN.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[gmmastros]

When you use the old fashioned syntax where you separate table names with columns, you are effectively creating a cross join.

Right or wrong.... the query is basically doing this.

delete GL20000 
from   GL00100
       Cross Join GL20000 
where  GL20000.ACTINDX = 1234 
       and GL00100.ACCTTYPE = 9 
       and GL20000.SOURCDOC = 'XYZ'

I agree that it is odd to use a cross join. My application has thousands of queries and I've only implemented cross joins a small handful of times in production code.

If I were you... I would let the third party vendor know about the hundreds of rows. Make them verify that the query is correct. Then, before running the query, make sure you have a good backup of the database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks gmmastros. That's exactly the reason I wanted to double-check. It's a cross-join. Restored the database elsewhere, showed the GP guy and he thought it's deleting too many rows, so we contacted the vendor and are waiting to hear.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[TheBugSlayer]

I now believe the query should be looking like this:

DELETE GL20000 
FROM GL00100, GL20000 
WHERE [COLOR=red][b]GL20000.ACTINDX = GL00100.ACTINDX[/b][/color]
	  AND GL20000.ACTINDX = 1234 
	  AND GL00100.ACCTTYPE = 9
	  AND GL20000.SOURCDOC = 'XYZ'

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).