delete duplicate records 1

[nickbrookes]

I've got a table with the following headings: id, pbid, date, activity, number and time. what i want to do is if the date, pbid and activity are all duplicated then to get rid of the duplication and just leave the one record.

the data looks like this:
id pbid date activity number time
1 nbrook 29/08 calls 25 60
2 nbrook 29/08 messages 20 40
3 pfarre 29/08 calls 25 60
4 nbrook 29/08 calls 01 03

i would only want to get rid of line 4 as the pbid, date and activity are ALL duplicated is this possible to use a query????

cheers for any light you can shed on this for me. i love you all

nick

 

[Tim1]

Hi Nick,

You can do this with an SQL command fairly easily. The trick is to make sure that you only delete the duplicate row, not all instances. Hence, something like this should do:

DELETE table
FROM table a,
table b <--- This would be the same table name twice, so opening two instances
WHERE a.pbid = b.pbid
AND a.date = b.date
AND a.activity = b.activity
AND a.id > b.id; <---- This will make sure that it is looking at two different rows

HTH

Tim