CODEWITHSUNDEEP
sqlnetezza
Rondel
Rondel

Reputation: 4951

How do I delete one record matching some criteria in SQL? (Netezza)

I've got some duplicate records in a table because as it turns out Netezza does not support constraint checks on primary keys. That being said, I have some records where the information is the exact same and I want to delete just ONE of them. I've tried doing

delete  from table_name where test_id=2025 limit 1

and also

delete  from table_name where test_id=2025 rowsetlimit 1

However neither option works. I get an error saying 

found 'limit'. Expecting a keyword

Is there any way to limit the records deleted by this query? I know I could just delete the record and reinsert it but that is a little tedious since I will have to do this multiple times.

Please note that this is not SQL Server or MySQL.This is for Netezza

Upvotes: 4

Views: 22203

Answers (6)

Dave Shuttleworth
Dave Shuttleworth

Reputation: 1

The GROUP BY 1,2,3,....,n will eliminate the dupes on the insert to the temp table

Upvotes: 0

Abhay
Abhay

Reputation: 1

Does the use rowid is allowed in Netezza...As far as my knowledge is concern i don't think this query will executed in Netezza...

Upvotes: -1

Yordan Georgiev
Yordan Georgiev

Reputation: 5430

  -- remove duplicates from the <<TableName>> table
  delete from <<TableName>> 
  where rowid not in 
  (
    select min(rowid) from <<TableName>>
    group by (col1,col2,col3) 
  );

Upvotes: 1

Teja
Teja

Reputation: 13534

The below query works for deleting duplicates from a table.

DELETE FROM YOURTABLE 
WHERE COLNAME1='XYZ' AND 
(
COLNAME1,
ROWID
)   
NOT IN
(
SELECT COLNAME1,
       MAX(ROWID)
FROM YOURTABLENAME
WHERE COLNAME = 'XYZ'
GROUP BY COLNAME1
)

Upvotes: 3

Dave Shuttleworth
Dave Shuttleworth

Reputation: 11

If the records are identical then you could do something like

CREATE TABLE DUPES as
SELECT col11,col2,col3,col....... coln from source_table where test_id = 2025
group by 
1,2,3..... n

DELETE FROM source_table where test_id = 2025

INSERT INTO Source_table select * from duoes

DROP TABLE DUPES

You could even create a sub-query to select all the test_ids HAVING COUNT(*) > 1 to automatically find the dupes in steps 1 and 3

Upvotes: 1

Teekin
Teekin

Reputation: 13289

If it doesn't support either "DELETE TOP 1" or the "LIMIT" keyword, you may end up having to do one of the following:

1) add some sort of an auto-incrementing column (like IDs), making each row unique. I don't know if you can do that in Netezza after the table has been created, though.

2) Programmatically read the entire table with some programming language, eliminate duplicates programmatically, then deleting all the rows and inserting them again. This might not be possible if they are references by other tables, in which case, you might have to temporarily remove the constraint.

I hope that helps. Please let us know.

And for future reference; this is why I personally always create an auto-incrementing ID field, even if I don't think I'll ever use it. :)

Upvotes: 4

Related Questions