Duplicate check & Delete Rows - SQL Server Forums

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

SQL Server Development (2000)

Duplicate check & Delete Rows

Author

Topic

rvan
Starting Member

28 Posts

Posted - 2007-05-23 : 19:08:17

Hello, Everyone

I'm in a process learn how to figure delete "old records" that match the new records. Can anyone helps or give a general ideal? I have two tables has no primary key & all fields contain "character":

table1 (old records) - businessname,address,city,state
table2 (new records) - duns,businessname,contactname,address,city,
state,plus4,crrt

My idea here is to cross reference the data match from "old records table1" with "new records table2" to remove the old data and keep new records.

Thanks

RV

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-23 : 22:41:23

Do you have sample data and the result you like to see?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-23 : 23:45:36

It is always best practice that, you should have a datetime column in all tables u create to store the date information when the row is inserted/modified.

If u have any column like this, it will be very easy to sort many problem.

In ur query u have specified that table one has old values and table 2 new ones and ur not having primary key also..i feel it is difficult...

if ur suer that the businessname column is unique then search a value from new table to old table , if found delete it else retain it...but if u have duplicate business name column then it will delete all entries.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-05-23 : 23:55:14

/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'

If you are going to use the subquery method 1 you'll also want to look into correlated subqueries.

http://www.databasejournal.com/features/mssql/article.php/3485291

rvan
Starting Member

28 Posts

Posted - 2007-06-01 : 01:41:03

quote:
Originally posted by rmiao

Do you have sample data and the result you like to see?

table1(old records) - businessname,address,city,state
Larry Johnson, 2630 S baker, Santa Ana, CA
Aarron Hank, 4450 Mark Lane, Lampson, KS

table2(new records) - businessname,address,city,state,plus4,crrt
Bob Hanson, 1250 Jackson St,Westchester,NY, 5984
Larry Johnson, 2630 S baker, Santa Ana, CA, 0909
Aarron Hank, 4450 Mark Lane, Lampson, KS, 2043

The situation here that I have 2 row records from "old records table1" match with the "new records table2" than will delete it in new table2.

I'm appreciating all your helps that I wants to create a profile for a reference in the future needs.

thanks you very much--

RV

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-01 : 07:17:44

see:

http://weblogs.sqlteam.com/jeffs/archive/2004/10/07/2190.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-06-01 : 07:41:22

Try this

Delete table1 from table1 a,table2 b
where a.businessname = b.businessname and a.address = b.address
and a.city =b.city and a.state =b.state

rvan
Starting Member

28 Posts

Posted - 2007-06-14 : 12:22:11

quote:
Originally posted by bpgupta

Try this

Delete table1 from table1 a,table2 b
where a.businessname = b.businessname and a.address = b.address
and a.city =b.city and a.state =b.state

Hello, bpgupta

I tested my current data that does work and I wants to said, thanks you.

RV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources