July 13, 2012 at 3:30 pm
I have performed the following query for someone to eliminate records in a table if they exist in either of two separate tables. I thought it was a pretty solid script but the person said that there were still duplicates. I also had others tell me that there were better ways to do it.(they didn't offer any suggestions) Is this an efficient, reasonable code?
/*
Select *
FROM Table_1a
WHERE Not Exists
(Select *
FROM Table_2 b
Where a.Field = b.Field
and a.Field_1 = b.Field_1 )
and Not exists
(Select *
FROM Table_3 c
Where a.Field = c.Field
and a.Field_1 = c.Field_1 )*/
I used L and R trim functions on the columns to make sure there were no empty spaces.
July 13, 2012 at 4:00 pm
SQL_Loser (7/13/2012)
I have performed the following query for someone to eliminate records in a table if they exist in either of two separate tables. I thought it was a pretty solid script but the person said that there were still duplicates. I also had others tell me that there were better ways to do it.(they didn't offer any suggestions) Is this an efficient, reasonable code?/*
Select *
FROM Table_1a
WHERE Not Exists
(Select *
FROM Table_2 b
Where a.Field = b.Field
and a.Field_1 = b.Field_1 )
and Not exists
(Select *
FROM Table_3 c
Where a.Field = c.Field
and a.Field_1 = c.Field_1 )*/
I used L and R trim functions on the columns to make sure there were no empty spaces.
Paraphrasing here:
You want all records in table_1 if it also exists in either Table_2 or table_3, correct?
Give this a try:
Select
*
FROM
Table_1 a
WHERE
exists(Select * FROM Table_2 b Where a.Field = b.Field and a.Field_1 = b.Field_1 )
OR
exists(Select * FROM Table_3 c Where a.Field = c.Field and a.Field_1 = c.Field_1 );
July 13, 2012 at 4:08 pm
No I do not want the records from table 1 if they exist in table 2 or table 3.
July 13, 2012 at 4:12 pm
one option is:
Select a.Field, a.Field_1 FROM Table_1 a left join Table_2 b
on (a.Field = b.Field and a.Field_1 = b.Field_1)
left join Table_3 c
on (a.Field = c.Field and a.Field_1 = c.Field_1)
where b.Field is null and c.Field is null
Saludos...
July 13, 2012 at 4:39 pm
Okay, let's try again. You want to delete records from table1 if the same record exists in either table2 or table3, correct?
July 13, 2012 at 4:43 pm
That is correct.
Thanks
July 13, 2012 at 5:13 pm
Lynn Pettis (7/13/2012)
Okay, let's try again. You want to delete records from table1 if the same record exists in either table2 or table3, correct?
It appears that the OP wants all records from Table 1 that does not exists in Table2 or Table3.
@SQLLoser, that is a pretty funny Name.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2012 at 3:24 am
SQL_Loser (7/13/2012)
That is correct.Thanks
Then the query I gave you should show you those records in the first table that exist in either table2 or table3.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply