How to DELETE all data in a table except for rows with specifc words?

user_15428798 0
Viewed 62

I am using pgadmin4 (version 2.1) and I'm attempting to remove some legacy data from a table without deleting rows with specific words in the description?

Basically I'm attempting to do something like this:

DELETE FROM schema.table WHERE column_description = all data EXCEPT FOR 'specific word'

Any ideas?

pgadmin-4 delete-row sql
1 Answers

Be sure to backup your data before attempting any deletes. But the idea would be:

DELETE FROM schema.table
    WHERE column_description IS DISTINCT FROM 'specific word';

This does not delete NULL values. If the column could have NULL values, you will want those deleted too. So a better construct is:

DELETE FROM schema.table
    WHERE column_description <> 'specific word';

Or, if the pattern could be part of the description, use NOT LIKE:

DELETE FROM schema.table
    WHERE column_description NOT LIKE '%specific word%';
user_11440350
Related