Join in a Delete qry - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Join in a Delete qry

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-22 : 13:45:52

Hi All,

I have one like:

delete from table1 inner join table2 on table1.field1 = table2.field1
and table1.field2 = value

but its throwing err:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

Can anybody help me

thanks,

Mahesh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-22 : 13:49:14

[code]
delete t1 -- or t2. it depends from which table you want to delete from
from table1 t1
inner join table2 t2 on t1.field1 = t2.field1
and t1.field2 = value
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-22 : 13:50:22

delete t1
from table1 t1
inner join table2 t2
on t1.field1 = t2.field1
where t1.field2 = value

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-23 : 05:22:23

Hey Spirit1 & Tara,

Thanks Buddies.

I have wrote the same qry but without using Alias. Can anybody tell me, does it mandetory to add ALIAS in such a case ... ?

Thanks in advance,

Mahesh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-23 : 05:23:48

yes. because if you don't use it the sql server doesn+'t know which table to use.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-23 : 05:25:16

quote:
Originally posted by spirit1

yes. because if you don't use it the sql server doesn+'t know which table to use.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

But in my qry i have used table names. Still ... ?

Mahesh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-23 : 05:33:20

well if you don't want to use aliases then you can use table name in this way:

delete table1 from table1 ...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources