Delete From ALL tables - SQL Server Forums

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

Delete From ALL tables

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-19 : 11:37:57

I have 5 tables in the same database named the following: do, name, olm, image, person, scr

They are all link together by and have the following field in each: FCN

I would like to delete the corresponding record in each table where the value of the field 'image_path' in the 'image' table is '999999.jpg'

What would the syntax be?

delete from do, name, olm, image, person, scr
where image.image_path = '999999.jpg'
innerjoin something????

Thanks

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-19 : 11:43:14

You need to do them individually, replacing table each time with the actual table name:

DELETE FROM <table> inner join image
on do.fcn = image.fcn
where image.image_path='9999999.jpg'

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 11:51:31

Scri[pt out all of the foreign keys

Drop the FK's

TRUNCATE The tables

Reapply the FK's

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-19 : 11:54:18

quote:
Originally posted by Zoroaster

DELETE FROM <table> inner join image
on do.fcn = image.fcn
where image.image_path='9999999.jpg'

When I run this:

DELETE FROM DO
inner join IMAGE
on DO.fcn = IMAGE.fcn
where IMAGE.IMAGE_PATH=' .jpg'

I get the following error:

Incorrect syntax near the keyword 'inner'.

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 11:59:23

Ah, misread that.

Make sure you do a backup now

if you post some DDL it might help us

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 12:00:09

I thought ALL Data, meant, ALL Data

Silly me

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-19 : 12:15:29

Ok, try this instead:
DELETE FROM DO
WHERE FCA in
(
SELECT FCA from IMAGE
WHERE IMAGE.IMAGE_PATH = '999999.jpg'
)

Future guru in the making.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-19 : 12:23:32

Zoroaster.... you are the man. Thank you for your help!

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 13:15:51

This may be more effecient


DELETE FROM DO d JOIN [IMAGE] i 
	 ON d.fca = i.FCA
        AND i.IMAGE_PATH = '999999.jpg'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-19 : 13:25:32

quote:
Originally posted by X002548

This may be more effecient

DELETE FROM DO d JOIN [IMAGE] i
ON d.fca = i.FCA
AND i.IMAGE_PATH = '999999.jpg'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

It won't accept that syntax when I try it.

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 13:28:06

damn, ok, hold on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 13:31:42

Use the JOIN syntax instead of the subquery way. Here ya go:

DELETE d
FROM DO d
JOIN [IMAGE] i
ON d.fca = i.FCA AND i.IMAGE_PATH = '999999.jpg'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-19 : 13:37:34

quote:
Originally posted by tkizer

Use the JOIN syntax instead of the subquery way. Here ya go:

DELETE d
FROM DO d
JOIN [IMAGE] i
ON d.fca = i.FCA AND i.IMAGE_PATH = '999999.jpg'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

That works, I don't like the syntax though. It seems silly that it doesn't work the way Brett did it, though I guess there is a reason.

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 13:45:35

That's just how deletes and updates work with joins. See BOL for the complete syntax.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-19 : 14:28:59

Right i'm not questioning the correctness of the syntax, I am just curious as to why it is not consistent with joins in selects - more of a question to Microsoft really.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources