0

I have table1 with columns: id, a, b
I have table2 with columns: a, b, c

I want to update table2´s value c on the condition that table1.a = table2.a and table1.b = table2.b
I want to insert into table2 values a, b and c if the above condition is not met (if no row exists in table2, that the fulfills the condition)
To “begin with” I only have the value table1.id and c.

Background: I have a html form posting only the id of table1 and a field with c.

Björn
  • 12,587
  • 12
  • 51
  • 70

1 Answers1

3

Use UPDATE, not REPLACE. REPLACE is used when you need to either update or insert depending on whether the row already exists. But since you're joining the tables, the row already has to exist, so there's no need for the insert option.

db.query('UPDATE table2 AS t2
        JOIN table1 AS t1 ON t1.a = t2.a AND t1.b = t2.b
        SET t2.c = ?
        WHERE t1.id = ?',
    [c, id]);

If you do need to handle the case where the row doesn't already exist, use INSERT with the ON DUPLICATE KEY UPDATE option.

db.query('INSERT INTO table2 (a, b, c)
        SELECT a, b, ?
        FROM table1 WHERE id = ?
        ON DUPLICATE KEY c = VALUES(c)',
    [c, id]);

Make sure there's a unique key on (a, b) in table2. If there isn't, add one:

ALTER TABLE table2 ADD UNIQUE KEY (a, b);

Here's another way to do it. You can use the UPDATE query at the top for the rows that have matches between the two tables, and the following INSERT query to add rows that don't have a match:

db.query('INSERT INTO table2 (a, b, c)
        SELECT t1.a, t1.b, ?
        FROM table1 AS t1
        LEFT JOIN table2 AS t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.id = ?
        WHERE t2.a IS NULL",
    [c, id]);

The SELECT query it uses is based on Return row only if value doesn't exist.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Ah thanks, I clarified in my question, that the row in table2 might not exist prior to the statement (it should then create it). – Björn Sep 12 '16 at 16:11
  • I just tried the ALTER TABLE table2 ADD UNIQUE KEY (a, b); command on my table and got the error: "Specified key was too long; max key length is 767 bytes". In my real world scenario I have 3 keys and together they are 1510 bytes. Is there some other away to accomplish that? – Björn Sep 12 '16 at 21:51
  • Why are you using such long strings as keys? Maybe you should put the strings in a reference table, and then use a foreign key in these tables. – Barmar Sep 12 '16 at 21:53
  • I am using them as such, because your answer suggested that :) a and b just happen to be a little longer... maybe there is a whole different solution to my original question. Will try to think of how to do it with a reference table and foreign key. – Björn Sep 12 '16 at 22:14
  • Your original `REPLACE` query expected them to be keys, didn't it? `REPLACE` needs to use a unique key to determine which rows to replace. – Barmar Sep 12 '16 at 22:14
  • oh sorry, you are right. just read about it. i thought it would just compare the strings a and b of each table against each other - and then replace or insert, based on the outcome of that comparison - or said better, thats what I wanted it to do. I think my original REPLACE query should better be ignored. – Björn Sep 12 '16 at 22:19
  • I just simplified my questions. Maybe you could have a peek. I need a solution that allows a and b to be longer strings. Cant I somehow compare them and then act on the result (insert or update) in one single statement? – Björn Sep 12 '16 at 23:36
  • @Hans Unless the columns you're matching are a unique key, you can't do it in a single query. You need `UPDATE` to update existing rows, `INSERT` to add new rows. – Barmar Sep 12 '16 at 23:37