Delete duplicates - 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

General SQL Server Forums

New to SQL Server Programming

Delete duplicates

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-01-13 : 11:26:52

have two tables lptrxfil_sql and lptrxhst_sql
Both have the same keys.

emp, shift, date, seq

I want to delete records from the lptrxfil_sql where the lptrxhst_sql has the same records.

This is what I tried but it doesn't like something.

delete from lptrxfil_sql
join lptrxhst_sql on lptrxfil_sql.emp_crew_no = lptrxhst_sql.emp_crew_no and
lptrxfil_sql.prd_st_dt = lptrxhst_sql.prd_st_dt and
lptrxfil_sql.shift = lptrxhst_sql.shift and
lptrxfil_sql.cc_date = lptrxhst_sql.cc_date and
lptrxfil_sql.seq_no = lptrxhst_sql.seq_no
where lptrxfil_sql.emp_crew_no = lptrxhst_sql.emp_crew_no and
lptrxfil_sql.prd_st_dt = lptrxhst_sql.prd_st_dt and
lptrxfil_sql.shift = lptrxhst_sql.shift and
lptrxfil_sql.cc_date = lptrxhst_sql.cc_date and
lptrxfil_sql.seq_no = lptrxhst_sql.seq_no

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 11:30:45

delete lptrxfil_sql from lptrxfil_sql
join lptrxhst_sql on lptrxfil_sql.emp_crew_no = lptrxhst_sql.emp_crew_no and
lptrxfil_sql.prd_st_dt = lptrxhst_sql.prd_st_dt and
lptrxfil_sql.shift = lptrxhst_sql.shift and
lptrxfil_sql.cc_date = lptrxhst_sql.cc_date and
lptrxfil_sql.seq_no = lptrxhst_sql.seq_no
where lptrxfil_sql.emp_crew_no = lptrxhst_sql.emp_crew_no and
lptrxfil_sql.prd_st_dt = lptrxhst_sql.prd_st_dt and
lptrxfil_sql.shift = lptrxhst_sql.shift and
lptrxfil_sql.cc_date = lptrxhst_sql.cc_date and
lptrxfil_sql.seq_no = lptrxhst_sql.seq_no

E 12°55'05.63"
N 56°04'39.26"

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-13 : 11:32:56

Hello;

Here is a simple script to delete duplicates entries. I use it to manage an inventory of Cisco (computer hardware) parts. Feel free to use it & modify it as you see fit. This presumes you have a primary key identified on the table.

DELETE
FROM TableA
WHERE ID
IN(select a.ID
from TableA as a,TableB AS b
where a.manufacturer=b.manufacturer And a.partnumber=b.partnumber
AND a.listprice=b.listprice) */

r&r

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-13 : 11:39:55

You can use Where Exists to make it simpler.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-13 : 12:10:16

and ANSI joins.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources