Solved

Remove only the records that don't don't exist before Update - SQL Server 2005

Posted on 2008-10-11
9
188 Views
Last Modified: 2010-04-21
Hello Experts,

I am updating Table2 from Table1 with the following synatax:

UPDATE Table2
SET col1 = t1.col1, col2 = t1.col2, col3 = t1.col3
FROM Table1 t1 INNER JOIN
    Table2 t2 ON t1.ID = t2.ID

This works great to update existing records...

I would like to remove any records in Table2 that do not exist in Table1 before the update.

In other words: Table1 will always be the full record range of Table2 unless there was a record removed from Table1. I always want to make sure that deleted records from Table1 are also deleted in Table2 upoh each update.

Does anyone know how to do this?

Thanks!



0
Comment
Question by:Saxitalis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22695282
This is one method using where clause.
DELETE FROM Table2
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE Table1.ID = Table2.ID)

Open in new window

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22695341
or u can use this statment


Delete Table2
from Table2 t2
right join Table1 t1
	on t2.ID = t1.ID
where 
 t2.ID is null

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22695377
You have that backwards, Cvijo123.

I was going to suggest a join earlier, but since you did just want to make sure it is correct one.

Table2 in this case has more records because you deleted from Table1, so you want a LEFT JOIN where t1.ID IS NULL.
Delete Table2
from Table2 t2
left join Table1 t1
	on t2.ID = t1.ID
where 
 t1.ID is null

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22695379
@Cvijo123, welcome by the way!
0
 

Author Comment

by:Saxitalis
ID: 22695393
mwvisa1,
Is there a benefit to using a join ( your second example) as opposed to a NOT EXISTS (your first example)?

Thanks!
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22695398
@mwvisa1:  :) yes u are right, table2 need to match table1 from wich record is deleted so its left join and t1.id is null :)

tnx for welcome note :)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22695408
Here is a good discussion on this: http://sql-server-performance.com/Community/forums/p/17162/100761.aspx

In a nutshell, I typically don't worry too much about the performance difference between the three methods as using a ID NOT IN (SELECT ID FROM Table1) is the other method as you will see some of the folks in the above don't either.  Technically some seem to favor the EXISTS/NOT EXISTS approach.  

I only mentioned the LEFT JOIN since join was suggested and was in incorrect syntax as I typically use what comes to mind that day. :) Probably bad of me to say, but it is the truth.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22695419
On systems older than 2005, you can use the more scientific approaches like using Query Execution Plan or turning on STATISTICS and seeing which produces best results; however, for SQL 2005, I haven't noticed enough of a difference to choose one over the other exclusively.

I think a lot of times, I usually end up using the NOT IN method as I am usually do analysis of data in the one table like say Table1 and so I have a query for that already and so it is convenient to just through a query in front of it that says SELECT col1 FROM Table2 WHERE id IN (my original query).

Probably more information than you wanted to know, but I have no life and enjoy typing.
0
 

Author Closing Comment

by:Saxitalis
ID: 31505354
Thank you Sir! - That was perfect
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question