StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is trickier than I first thought, since you want to join the same table multiple times, and the only connection is the updated table itself:</p> <pre><code>UPDATE table_a a SET owner1_surname = b1.owner_surname ,owner1_othername = b1.owner_othername ,owner2_surname = b2.owner_surname ,owner2_othername = b2.owner_othername ,owner3_surname = b3.owner_surname ,owner3_othername = b3.owner_othername FROM table_a x LEFT JOIN owners_distinct b1 ON b1.b.owner = x.owner1 LEFT JOIN owners_distinct b2 ON b2.b.owner = x.owner2 LEFT JOIN owners_distinct b2 ON b3.b.owner = x.owner3 WHERE x.table_a_id = a.table_a_id </code></pre> <p>Where <code>table_a_id</code> is the primary key of <code>table_a</code>. Normally you don't have to join the table another time, but in this situation you need it for the join <em>before</em> you can link to the updated table.</p> <p>I use <code>LEFT JOIN</code>, in order to prevent the whole update for a row from failing if one of the three owners cannot be found in <code>owners_distinct</code>.</p> <h3>Database design</h3> <p>Are you sure you need all the <strong>redundant data</strong> in <code>table_a</code>? The canonical way in a normalized schema would be to only store the <code>foreign keys</code> (<code>owner1</code>, <code>owner2</code>, <code>owner3</code>), and fetch details of the name on demand with a <code>JOIN</code> in a <code>SELECT</code>. Remove all those columns you are updating altogether. Of course, there are always exceptions to the rule ...</p> <h3>No unique key?</h3> <p>This shouldn't happen to begin with. You should add a surrogate primary key like:</p> <pre><code>ALTER TABLE table_a ADD table_a_id serial PRIMARY KEY; </code></pre> <p>More about that in the related answer:<br> <a href="https://stackoverflow.com/questions/10516661/do-i-need-a-primary-key-for-my-table-which-has-a-unique-composite-4-columns/10518219#10518219">Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?</a></p> <h3>Solution without unique key</h3> <p>Anyway, here is a way to make this update regardless of <em>any</em> unique column:</p> <pre><code>UPDATE table_a a SET owner1_surname = b1.owner_surname ,owner1_othername = b1.owner_othername ,owner2_surname = b2.owner_surname ,owner2_othername = b2.owner_othername ,owner3_surname = b3.owner_surname ,owner3_othername = b3.owner_othername FROM (SELECT DISTINCT owner1, owner2, owner3 FROM table_a) x LEFT JOIN owners_distinct b1 ON b1.b.owner = x.owner1 LEFT JOIN owners_distinct b2 ON b2.b.owner = x.owner2 LEFT JOIN owners_distinct b2 ON b3.b.owner = x.owner3 WHERE x.owner1 = a.owner1 AND x.owner2 = a.owner2 AND x.owner3 = a.owner3; </code></pre> <p>The point is: we only need each combination of <code>(owner1, owner2, owner3)</code> once.</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