Delete based on other table values

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 2012 Forums

Transact-SQL (2012)

Delete based on other table values

Author

Topic

Loneliness
Starting Member

10 Posts

Posted - 2013-12-13 : 04:00:14

Hi,
i have 2 tables that have 3 common fields:

table1
a varchar(50)
c varchar(50)
b varchar(50)
..
..
..

table2
a varchar(50)
c varchar(50)
b varchar(50)
..
..
..

i want to delete from table2 all the rows that have table2.a=table1.a and table2.b=table1.b and table2.c=table1.c
How can i do this?
Thanks in advance

tarun_kumar07
Starting Member

4 Posts

Posted - 2013-12-13 : 04:46:17

It can be done with simple where clause as below.

Delete from Table2 where
Table2.a in (Select Table1.a from Table1)
and
Table2.b in (Select Table1.b from Table1)
and
Table2.c in (Select Table1.c from Table1)
...

Similarly for all the common columns.

Hope this answers your query.
Thanks

Loneliness
Starting Member

10 Posts

Posted - 2013-12-13 : 05:31:52

quote:
Originally posted by tarun_kumar07

It can be done with simple where clause as below.

Delete from Table2 where
Table2.a in (Select Table1.a from Table1)
and
Table2.b in (Select Table1.b from Table1)
and
Table2.c in (Select Table1.c from Table1)
...

Similarly for all the common columns.

Hope this answers your query.
Thanks

I am afraid it does'nt Kumar...because this way if i have in Table2 row number 3 for example, that have field a maches with field a of the row number 1 in Table1,
and field b that maches with field b of another row of Table2 the row would be deleted the same.
My 2 tables are made like below:

Table1:
code:1
name:james
age: 30
****
code:2
name:jack
age: 34
------------
Table2

code:1
name:jack
age: 30
****
code:2
name:james
age: 34
*******
code:1
name:james
age: 30
****

With your query all rows of Table2 would be deleted, but i need to delete only the third row because it has all the 3 fields that maches with the 3 fields of row 1 in Table1

tarun_kumar07
Starting Member

4 Posts

Posted - 2013-12-13 : 06:06:42

In that case,following should work:

Delete T2 table2 as T2
Inner join
Table1 as T1 on join_condition
where T1.code=T2.code
and T1.age=T2.age
and T1.name=T2.name

Thanks

Loneliness
Starting Member

10 Posts

Posted - 2013-12-13 : 06:59:13

quote:
Originally posted by tarun_kumar07

In that case,following should work:

Delete T2 table2 as T2
Inner join
Table1 as T1 on join_condition
where T1.code=T2.code
and T1.age=T2.age
and T1.name=T2.name

Thanks

still not working...it gives me this error:
"Incorrect syntax near 'table2'" i tried also with "Delete table2 as T2...." and the error is "Incorrect syntax near the keyword 'as'"

huskee
Starting Member

2 Posts

Posted - 2013-12-13 : 09:23:04

I know it's not very neat but still...
delete te from #tab2 te where (te.age = (select t2.age from #tab2 t2 inner join #tab1 t1 on t1.age = t2.age and t1.code = t2.code and t1.name = t2.name)
and te.code = (select t2.code from #tab2 t2 inner join #tab1 t1 on t1.age = t2.age and t1.code = t2.code and t1.name = t2.name)
and te.name = (select t2.name from #tab2 t2 inner join #tab1 t1 on t1.age = t2.age and t1.code = t2.code and t1.name = t2.name))

Loneliness
Starting Member

10 Posts

Posted - 2013-12-13 : 10:21:16

quote:
Originally posted by huskee

I know it's not very neat but still...
delete te from #tab2 te where (te.age = (select t2.age from #tab2 t2 inner join #tab1 t1 on t1.age = t2.age and t1.code = t2.code and t1.name = t2.name)
and te.code = (select t2.code from #tab2 t2 inner join #tab1 t1 on t1.age = t2.age and t1.code = t2.code and t1.name = t2.name)
and te.name = (select t2.name from #tab2 t2 inner join #tab1 t1 on t1.age = t2.age and t1.code = t2.code and t1.name = t2.name))

Thank you very much Huskee, you solution works fine...i also found this other solution that is a little bet less complicated:

DELETE T2
from TABLE2 T2 inner join TABLE1 as T1
on T2.CODE =T1.CODE and
T2.NAME = T1.NAME and
T2.AGE = T1.AGE

Thanx also to Kumar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources