Update multiple rows with one query using a different rows value

Question

I didn't know how to clearly title this.

After joining a table on itself using shared_id I get the following data.

a.id   | a.shared_id | a.key     | a.value         | b.id   | b.shared_id | b.key       | b.value
198131 | 6044        | unique-id | E15SJOEMGRMH013 | 198281 |6044         | _unique_id  |

This is the result from:

SELECT * FROM
(SELECT * FROM table1 WHERE key='unique-id') AS a
JOIN
(SELECT * FROM table1 WHERE key='_unique_id') AS b
ON a.shared_id = b.shared_id
WHERE a.value!='' AND a.value!=b.value AND a.key='unique-id'
;

What I need is an update statement that will set b.value to a.values value.So in this 1 row example I want b.value to be E15SJOEMGRMH013.

Now remember a.id and b.id are NOT the same as the keys are called something slightly different but they should hold the same value. There are several hundred rows so I would like to do this in one update query.

Please show what you tried. You basically take your join, replace the SELECT <columns> FROM <tablename> b with UPDATE <tablename> , and add SET b.value = a.value at the end. — Barmar
– Barmar, Commented Feb 5, 2015 at 19:24
@Barmar query added. im not sure how to make it an update statement because of the multiple rows with different ids and yet the same shared_id. — Shawn
– Shawn, Commented Feb 5, 2015 at 19:29

Barmar · Accepted Answer · 2015-02-05 19:34:14Z

2

You don't need the sub-queries, and your use of them is probably why you couldn't figure out the corresponding UPDATE query.

UPDATE table1 AS a
JOIN table1 AS b ON a.shared_id = b.shared_id
SET b.value = a.value
WHERE a.key = 'unique-id' AND b.key = '_unique_id'
    AND a.value != ''

The SELECT should be simply:

SELECT *
FROM table1 AS a
JOIN table1 AS b ON a.shared_id = b.shared_id AND a.value != b.value
WHERE a.key = 'unique-id' AND b.key = '_unique_id'
    AND a.value != ''

You don't need the a.value != b.value clause in the UPDATE -- if they're the same, the UPDATE doesn't do anything.

answered Feb 5, 2015 at 19:34

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shawn Over a year ago

Wow yes this is much easier then the way I built it out I see my logic error now. Thanks!

Collectives™ on Stack Overflow

Update multiple rows with one query using a different rows value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related