Deletion Script - 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

Deletion Script

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-03-05 : 16:02:26

I'm sure it's simple, but why won't this work?

DELETE FROM SORELS_CST where sorels_cst.fkey_id in 

(select sorels_cst.fkey_id from sorels inner join sorels on sorels_cst.fkey_id = sorels.identity_column
where sorels.fsono = '290428'
AND ltrim(rtrim(sorels.finumber)) = '3')

Error 3/5/2009 3:00:49 PM 0:00:00.000 SQL Server Database Error: Tables or functions 'sorels' and 'sorels' have the same exposed names. Use correlation names to distinguish them. 24 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-05 : 16:07:56

DELETE sc
FROM SORELS_CST sc
INNER JOIN sorels s
ON sc.fkey_id = s.identity_column
WHERE s.fsono = '290428' AND ltrim(rtrim(s.finumber)) = '3'

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

Subscribe to my blog

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-03-05 : 16:23:26

So, I needed to use aliases. Thanks Goddess.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-03-05 : 16:26:50

My fiancee ran into the same issue yesterday - I had to research a little bit to find out that aliasing tables on deletion is necessary.

Some days you're the dog, and some days you're the fire hydrant.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-05 : 17:11:14

quote:
Originally posted by DavidChel

So, I needed to use aliases.

Yes aliases but also you should use joins instead of subqueries where possible due to the execution plan. You can compare the two in a SSMS/QA window by viewing the execution plans for both in the same batch.

quote:
Originally posted by DavidChel
Thanks Goddess.

You're welcome.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources