Delete Records are Match on different tables

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

General SQL Server Forums

New to SQL Server Programming

Delete Records are Match on different tables

Author

Topic

rvan
Starting Member

28 Posts

Posted - 2007-02-19 : 17:59:05

Hello, Everyone

I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4

2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.

****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.

/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-19 : 18:53:27

you want to delete from old_data or current data ?

rvan
Starting Member

28 Posts

Posted - 2007-02-19 : 19:41:59

quote:
Originally posted by khtan

you want to delete from old_data or current data ?

KH

Hey, Khtan

I would like to delete from 'current_data' that any records are reference the 'old_data' table and than keep remain good records.

thank you for your reply.

RV

scmay
Starting Member

22 Posts

Posted - 2007-02-19 : 20:27:44

You might have problems if you have NULL values in the record. Try replacing the NULL values with a default zero(0) or something.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-19 : 22:36:39

[code]
delete d
from current_data d inner join old_data o
on o.FirstName = d.FirstName
and o.LastName = d.LastName
and o.Address = d.Address
[/code]

rvan
Starting Member

28 Posts

Posted - 2007-02-20 : 14:18:38

quote:

Originally posted by khtan


delete d
from   current_data d inner join old_data o
       on  o.FirstName = d.FirstName 
       and o.LastName  = d.LastName
       and o.Address   = d.Address

=============================================
Thank you......Khtan

You are the best that helping me solve my problem.
Questions, Is there a way write procedure to delete match records and save the remain good records?

RV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 18:18:59

what do you mean by "delete match records and save the remain good records" ? What is good records ?

The query i posted, will delete matched records.

rvan
Starting Member

28 Posts

Posted - 2007-02-20 : 20:55:35

quote:
Originally posted by khtan

what do you mean by "delete match records and save the remain good records" ? What is good records ?

The query i posted, will delete matched records.

KH

Khtan,
Yes, I do got your query statement it does help me to the question. But another case I was interested know how to write store procedure and would you please provide some tips.

thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 22:28:05

[code]
create procedure <stored procedure name>
as
begin

delete d
from current_data d inner join old_data o
on o.FirstName = d.FirstName
and o.LastName = d.LastName
and o.Address = d.Address

end
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources