Script to delete null value rows in data extension [closed]

Question

I am trying to create a script that deletes any rows in my data extension that have a certain field value of null. SSJS and Ampscript seem to be able to do this, but this is my first Marketing Cloud script and I have no Ampscript/little JavaScript experience. Any advice is appreciated!

Answer 1

When dealing with NULL values I usually tackle this with a combination of SQL query and SSJS script. If you are trying to delete a row based on an actual value then you can easily skip the SQL step.

1. Create a SQL query to SELECT any record where the certain field IS NULL and set a field on that record to something like 'FlagRemove' on UPDATE.
2. Run a SSJS script like var rows = Platform.Function.DeleteData('Data_extension_name',['FlagRemove'],['true']);

Answer 2

The simplest way I have found this is to not even use any AMPSCript or SSJS.

I use 2 SQL statements (and 2 DEs) that would run in an automation.

SQL 1:

SELECT yourColumns FROM yourOriginalDE WHERE notNullColumn IS NOT NULL

Have this target the secondDE and have the ACTION set to overwrite.

SQL 2:

SELECT * FROM secondDE

Have this target yourOriginalDE and have the ACTION set to overwrite.

With the action of overwrite, it will delete all records and then fill in only those that were pulled in the SQL query. Thus, removing all the NULL values without a DELETE function.

By using SQL only I find it usually runs much quicker than using AMPScript or SSJS and it also is much simpler to write/edit.