Solved

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

Posted on 2008-10-11
9
185 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
  • 5
  • 2
  • 2
9 Comments
 
LVL 59

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 59

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 59

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 59

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 59

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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