Databases
StackOverflow2013
Postsdbo
POMySQL: Updating a left-joined view, where right table has no matching data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: Updating a left-joined view, where right table has no matching data
    text
    copied!<p>I'm curious to know if this is actually possible...</p> <p>In this simplified example, I have:</p> <pre><code>Table_A: Table_B: +------+--------+ +------+--------+ | id | data_1 | | id | data_2 | +------+--------+ +------+--------+ | 1 | Hello | | 1 | There | +------+--------+ +------+--------+ | 2 | Hi | +------+--------+ </code></pre> <p>A MySQL View (<code>CREATE VIEW...</code>) called <code>tables</code> is:</p> <pre><code>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 </code></pre> <p>So, a simple <code>SELECT * FROM tables</code> will return:</p> <pre><code>+------+--------+--------+ | id | data_1 | data_2 | +------+--------+--------+ | 1 | Hello | There | +------+--------+--------+ | 2 | Hi | NULL | +------+--------+--------+ </code></pre> <p>An update to existing data is fine. Ie., <code>UPDATE tables SET data_2='World' WHERE id=1</code> completes and the result for id 1 would be:</p> <pre><code>+------+--------+--------+ | id | data_1 | data_2 | +------+--------+--------+ | 1 | Hello | World | +------+--------+--------+ </code></pre> <p>But what about using an <code>UPDATE</code> statement on the right table (<code>table_b</code>) if it does not have any data matching the id? Using the same example as earlier, <code>UPDATE tables SET data_2='John' WHERE id=2</code> will result in 0 Rows matched (and therefore, <code>tables</code> will still return <code>NULL</code> instead of <code>John</code> in the <code>data_2</code> field). </p> <pre><code>+------+--------+--------+ | id | data_1 | data_2 | +------+--------+--------+ | 2 | Hi | NULL | +------+--------+--------+ </code></pre> <p>Is there a way to make this work on the <code>tables</code> view, without having to touch the <code>table_b</code> directly?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload