On 9/5/07, Håkan Jacobsson <hakan.jacobsson99@xxxxxxxxxxxx> wrote: > Hi, > > I want to create a DELETE statement which deletes duplicates > in a table. > > That is, I want to remove all rows - but one - having three > columns with the same data (more columns exist and there the > data varies). > For example: > column1 > column2 > column3 > column4 > column5 > > column2 = 'test', column3 = 'hey' and column4 IS NULL for > several rows in the table. I want to keep just one of those > rows. > > Is this possible? I can't figure it out, so any help MUCH > appreciated! when removing duplicates, I find it is usually better to look at this problem backwards...you want to select out the data you want to keep, truncate the original table, and insert select the data back in. What isn't exactly clear from your question is if you are interested in only particular fields or if you want to throw out based on any columns (nut just 2, 3, and 4). If so, this is a highly irregular (and interesting) problem, and should prove difficult to make efficient. If you are only interested in three particular columns, then it's easy. 1. select out data you want to keep using create table scratch SELECT DISTINCT ON or GROUP BY into scratch 2. truncate main table 3. insert into main select * from scratch for a more specific answer, you will have to provide some more detail, especially regarding exactly how you determine two rows as being 'duplicates'. merlin ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
