CODEWITHSUNDEEP
mysqlsqlinserton-duplicate-key
Wistar
Wistar

Reputation: 3790

SQL INSERT without duplicate (no primary or unique keys)

Let's consider that I have a Table A that I use to generate a Table B. I have no primary key or unique key I could use for a ON DUPLICATE KEY.

My Table B has 3 columns: A, B, and C.

I want to INSERT this only if the row already doesn't exist. I can determine if the row exist be the combination of A and B. I use a WHERE NOT EXISTS statement to do so.

INSERT INTO TableB
(A, B, C)
SELECT 
SUM(A),
SUM(B),
SUM(C)
FROM TableA
WHERE NOT EXISTS (SELECT * FROM TABLE B WHERE A=A AND B=B)
GROUP BY A, B

If Table B is empty, the row are inserted. However, as soon as Table B has a record, no rows are inserted even if they doesn't already exist in Table B.

Upvotes: 3

Views: 3559

Answers (2)

Wistar
Wistar

Reputation: 3790

I think that the best and faster way to do this is to apply a UNIQUE Constraint on A + B.

Alter TABLE TableB ADD CONSTRAINT unique_A_B UNIQUE(A,B);

Then use ON DUPLICATE KEY UPDATE:

INSERT INTO TableB (A,B,C)
SELECT 
      SUM(A),
      SUM(B),
      SUM(C)
    FROM TableA 
GROUP BY A, B
ON DUPLICATE KEY UPDATE C=SUM(C);

For the example purpose I updated C but I guess that in a real situation you'd want to update the Update Timestamp of your row with Now().

Upvotes: 1

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

You should prefix the columns from the outer query with its alias in the subquery:

INSERT INTO TableB (A, B, C)
  SELECT 
      SUM(A),
      SUM(B),
      SUM(C)
    FROM TableA ta
  WHERE NOT EXISTS (SELECT * FROM TableB tb WHERE tb.A=ta.A AND tb.B=ta.B)
  GROUP BY A, B

The way you wrote it, you were comparing values from table TableB with values from TableB (each row with itself), so when you inserted at least one row, the condition that "there is no row that is equal to itself" was never true.

Upvotes: 5

Related Questions