Delete the data which exist in one table and not exist in the other one?

Question

I want to select all rows which exist in specific table and not exist in the other table to delete them .

I write the following query but i get rows exist in both tables !!

SELECT  UNIQUE b.values_key FROM request_fo a INNER JOIN rm_trans  b 
ON b.values_key != a.req_year || ',' || a.req_ser
AND b.taskcode = 19
AND  b.values_key IS NOT NULL
AND a.req_year IS NOT NULL 
AND a.req_ser IS NOT NULL

---

I want to use the following to delete the data exist in request_fo and not exist in rm_trans :

DELETE request_fo 
FROM request_fo a
INNER JOIN
rm_trans  b 
ON b.values_key != a.req_year || ',' || a.req_ser
AND b.taskcode = 19
AND  b.values_key IS NOT NULL
AND a.req_year IS NOT NULL 
AND a.req_ser IS NOT NULL

Is this right ??

Solution 1

You have to think in sets when you want to perform this sort of operation. You're using != when you need NOT IN (...) or NOT EXISTS (...).

Your clause b.values_key != a.req_year || ',' || a.req_ser is comparing every row from a with every row in b. In most cases, they won't equate. But there will be many examples where the values_key in this particular row matches a.req_year || ',' || a.req_ser in other rows of a than the one currently being compared. That's why you're getting data returned that does exist in both tables.

You want to try something like this:

DELETE FROM a
  WHERE NOT EXISTS (SELECT 1 FROM b
                    WHERE b.values_key = a.req_year || ',' || a.req_ser
                       AND b.task_code = 19)

You should also run that as SELECT * FROM a WHERE NOT EXISTS (...) to verify it first. I would.

Solution 2

If you can not use the join syntax you can also use a subselect.

delete from t1
where t1 = something
and not exists
(
   select 1
   from t2
   where t1.key = t2.key
)

Solution 3

Here is the T-SQL syntax to do so:

Use LEFT JOIN instead, with b.values_key IS NULL predicate in the WHERE clause, and use the alias a instead of the table name in the DELETE clause:

DELETE a 
FROM request_fo a
LEFT JOIN rm_trans  b  ON b.values_key = a.req_year + ',' + a.req_ser
                      AND b.taskcode = 19
WHERE  b.values_key IS  NULL;

See it in action here:

• SQL Fiddle Demo.

Assuming that req_ser and req_year are of data type string, if they are integer cast them.

---

How it works?

LEFT JOIN will include those unmatched rows from the first table request_fo which is on the left of the LEFT JOIN, even if there is no entries satisfy the JOIN consition, and in this case the values_key value will be NULL for those unmatched rows.

For instance, see this demo, the 2013 4 from the first table doesn't exist in the second table so its values_key will be NULL, so adding WHERE b.values_key IS NULL will give you those that is found in the fist table and not found in the second table.