Deleting rows in a table with no primary key constraint

Question

There are two tables A and B with same structure (number of columns, column names etc.). There is no primary key constraint for both A and B. Some of the columns values can be null but not mentioned as a constraint.

Creation of table looks like below

CREATE TABLE IF NOT EXISTS TableA 
( col1 INT,
  col2 VARCHAR(50)
  col3 BIGINT )

I need to delete rows in A which are in B i.e A = A - B

There are around 100 columns in the original table (I have simplified it above). So listing all the columns is not desirable.

How do I do this task?

I had to add rows from another table C which I did by using INSERT INTO.

 INSERT INTO tableA VALUES 
 ( 
   SELECT * From tableC 
   EXCEPT 
   SELECT * from tableA 
 )

There must be a unique key made up of one or more columns or a common set of keys for A and B, use that. — Joakim Danielson, Mar 15 '18 at 08:46
Add some sample table data and the expected result. (As formatted text, not images.) Also show us your current query attempt. — jarlh, Mar 15 '18 at 08:46
Possible duplicate of [Delete all rows in a table based on another table](https://stackoverflow.com/questions/1590799/delete-all-rows-in-a-table-based-on-another-table) — Smita Ahinave, Mar 15 '18 at 08:56

score 0 · Answer 1 · answered Mar 15 '18 at 08:52

Use Left Join Of Table A with Table B and select all columns in "ON" condition while joining then Select those rows or records which have null values in Table B's any column. For Example:

TABLE A:

id  name    data
1   DAN     123
2   ANTONY  234
3   DAN     456
4   DAN     856
5   JOSEPH  546
6   ANTONY  784
7   JOSEPH  896

TABLE B:

id  name    data
1   DAN     123
5   JOSEPH  546
7   JOSEPH  896

QUERY for the above problem is:

SELECT A.id,A.name,A.data
FROM A
LEFT JOIN B
ON A.id = B.id
AND A.name = B.name
AND A.data = b.data
WHERE B.id IS NULL;

Result is:

id  name    data
2   ANTONY  234
3   DAN     456
4   DAN     856
6   ANTONY  784

You can also check this on below link:

http://sqlfiddle.com/#!9/d9e34b/4

score 0 · Answer 2 · answered Mar 15 '18 at 08:55

0

 delete from A where (column1, column2) not in 
((
select column1, column2from A
minus 
select column1, column2 from B
))

include all the columns from the table in select statement. As you have mentioned both table has same number of columns and names this query will work for you

answered Mar 15 '18 at 08:55

Sas

278
1
12

I thought of this but this does not handle NULL values. Also the production table (I simplified it in the question) has around 100 columns and writing all the column names is tedious. – Vijay Giri Mar 15 '18 at 09:12
in that case you have to find a column based on that we can delete. – Sas Mar 15 '18 at 09:17

score 0 · Answer 3 · edited Mar 15 '18 at 09:46

0

You can delete data from A using inner join between A and B table,

     Delete A 
       From A 
 Inner Join B 
         On A.id   = B.Id 
        And A.name = B.name 
        And A.Data = b.data;

Try following Demo

Demo

edited Mar 15 '18 at 09:46

Prabhat Sinha

1,500
20
32

answered Mar 15 '18 at 09:14

Alpesh Jikadra

1,692
3
18
38

This will work if null cases are handled like `(A.id = B.id or A.id is null and B.id is null)` . The original table has around 100 columns. So writing all the columns can be tedious. – Vijay Giri Mar 15 '18 at 09:30
Yes, If you want data by common id then this work fine. – Alpesh Jikadra Mar 15 '18 at 09:31

Deleting rows in a table with no primary key constraint

3 Answers3