MySQL: Updating a left-joined view, where right table has no matching data.

Question

I'm curious to know if this is actually possible...

In this simplified example, I have:

Table_A:           Table_B:

+------+--------+  +------+--------+
| id   | data_1 |  | id   | data_2 |
+------+--------+  +------+--------+
| 1    | Hello  |  | 1    | There  |
+------+--------+  +------+--------+
| 2    | Hi     |
+------+--------+

A MySQL View (CREATE VIEW...) called tables is:

SELECT table_a.id AS id, 
 table_a.data_1 AS data_1, 
 table_b.data_2 AS data_2
FROM table_a
LEFT JOIN table_b
ON table_a.id=table_b.id

So, a simple SELECT * FROM tables will return:

+------+--------+--------+
| id   | data_1 | data_2 |
+------+--------+--------+
| 1    | Hello  | There  |
+------+--------+--------+
| 2    | Hi     | NULL   |
+------+--------+--------+

An update to existing data is fine. Ie., UPDATE tables SET data_2='World' WHERE id=1 completes and the result for id 1 would be:

+------+--------+--------+
| id   | data_1 | data_2 |
+------+--------+--------+
| 1    | Hello  | World  |
+------+--------+--------+

But what about using an UPDATE statement on the right table (table_b) if it does not have any data matching the id? Using the same example as earlier, UPDATE tables SET data_2='John' WHERE id=2 will result in 0 Rows matched (and therefore, tables will still return NULL instead of John in the data_2 field).

+------+--------+--------+
| id   | data_1 | data_2 |
+------+--------+--------+
| 2    | Hi     | NULL   |
+------+--------+--------+

Is there a way to make this work on the tables view, without having to touch the table_b directly?

Answer 1

No, since there is no row to UPDATE.

You want an INSERT here, that's why you need to INSERT a row into table_b directly.

If your table_b.id is a PRIMARY KEY or a UNIQUE KEY, you can do the following:

INSERT
INTO    table_b (id, data2)
VALUES  (2, 'There')

, which will UPDATE the corresponding row if id = 2 already exists in the table, or INSERT it if it doesn't.

Answer 2

This is actually an insert, not an update, since no row exists in table_b with id=2. You could do it with two separate statements, one to do the update (with an inner join), and another to do the insert. I doubt it's possible in a single statement.

Answer 3

If you want to do what you're suggesting, it's best to maintain a foreign key in table_b even if there isn't a value for data_2. As answered already it will take two statements. First you'd have to backfill table_b with missing keys from table_a. Something like this should work.

INSERT INTO table_b(id)
SELECT table_a.id, table_b.id
FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.id
WHERE table_b.id is null;

Once you've completed the backfill, then your original update statement would work.