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

Question

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

Answer 1

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. :)