Delete rows that contain NULLs if row with ID already exists-CodePudding

I have a table as shown below.

If a product id has been entered into the table with a description, I want to delete all other rows with the same product ids where the description is NULL, leaving only the product id with a description. In the table below, I want to delete ROW_ID = 1 and keep ROW_ID = 2.

Furthermore, if a row is present with product id with a NULL description but no other matching product ids, I want to leave that row in the table. In the table below leaving ROW_ID = 3 as it is.

row_id	product_id	product_description	time_stamp
1	000001	null	2012-12-22 20:00:00
2	000001	item description	2012-12-23 21:00:00
3	000002	null	2012-12-23 21:00:00

The resulting table would look like this:

row_id	product_id	product_description	time_stamp
2	000001	item description	2012-12-23 21:00:00
3	000002	null	2012-12-23 21:00:00

CodePudding user response：

You can try like this:

WITH    cte
AS ( SELECT   * ,ROW_NUMBER() OVER ( PARTITION BY product_id order by time_stamp desc) as rn
    FROM     t)

DELETE FROM cte
WHERE  rn>1 and cte.product_description is null;

select * from t;

FIDDLE DEMO

CodePudding user response：

Try something like this assuming the table is named products:

DELETE FROM products as p
WHERE p.product_description is null
AND EXISTS (
  SELECT * FROM products as p2 WHERE p2.product_id = p.product_id AND p2.product_description IS NOT NULL
);

The EXISTS operator will return true if the subselect returns greater than 0 rows.