NewBie: Problems with DELETE Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NewBie: Problems with DELETE Statement

Author

Topic

mattwilkinson
Starting Member

3 Posts

Posted - 2006-01-11 : 06:56:09

I am trying to delete records in a table if a flag set in a linked table is set, but seem to be having problems with it. Neither of the following work:

DELETE FROM DSCar WHERE SerialNum IS NOT NULL
AND DSCar.SerialNum=DSOrds.SerialNum
AND DSOrds.StatusFlag='3'

This reports DSOrds.SerialNum could not be bound

DELETE FROM DSCar
INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum
WHERE DSOrds.StatusFlag='3'

This reports Incorrect syntax near the keyword 'INNER'

But a similar SELECT statement works fine:

SELECT * FROM DSCar
INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum
WHERE DSOrds.StatusFlag='3'

I think it is something simple but am having a real head scratcher.

Thanks

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2006-01-11 : 07:00:53

try:

DELETE DSCAR FROM DSCar,DSORDS WHERE dscar.SerialNum IS NOT NULL
AND DSCar.SerialNum=DSOrds.SerialNum
AND DSOrds.StatusFlag='3'

http://www.sqljunkies.com/weblog/sqldude
http://harshals.blogspot.com

mattwilkinson
Starting Member

3 Posts

Posted - 2006-01-11 : 07:31:49

Bingo!

Why does the SELECT Statement work but the DELETE not work though?

I thought they were supposed to be virtually identical to allow you to formulate a SELECT to see what rows you are about the delete and change to DELETE to actual delete them?

----------
http://freefallsoftware.co.uk

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-11 : 08:49:51

they are

you do this:
select ...
from ... join ... join ... -- you can have whatever join you wish here
where ....

then you simply change
select ....
to
delete TableName -- table name you want to delete from
and that's it.

of course you have to take into account all of rhe referential integrity constraints.


begin tran

select 	*
from 	Orders O
	join [Order Details] OD on OD.OrderID = O.OrderID
where 	O.OrderID < 10260

delete 	OD
from 	Orders O
	join [Order Details] OD on OD.OrderID = O.OrderID
where 	O.OrderID < 10260

select 	*
from 	Orders O
	join [Order Details] OD on OD.OrderID = O.OrderID
where 	O.OrderID < 10260

rollback

Go with the flow & have fun! Else fight the flow

mattwilkinson
Starting Member

3 Posts

Posted - 2006-01-11 : 11:24:25

OK - Thanks

----------
http://freefallsoftware.co.uk

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-11 : 11:59:41

You had incorrect syntax on the DELETE statement.

Try this:

DELETE FROM DSCar
FROM
	DSCar 
	INNER JOIN
	DSOrds
	ON DSCar.SerialNum=DSOrds.SerialNum 
WHERE
	DSOrds.StatusFlag='3'

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 00:12:08

DELETE FROM DSCar
INNER JOIN DSOrds ON DSCar.SerialNum=DSOrds.SerialNum
WHERE DSOrds.StatusFlag='3'

It is better practice to use alias names for the tables


DELETE DS
	from DSCar DS
	INNER JOIN DSOrds DSO ON DS.SerialNum=DSO.SerialNum 
WHERE DSO.StatusFlag='3'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources