CODEWITHSUNDEEP
mysqlsql-updatematchrows
ProgrammerGirl
ProgrammerGirl

Reputation: 3223

How to UPDATE MySQL Rows with other values in the same column?

I have a table in MySQL that has some wrong data appearing as 0 in col3 instead of the correct value.

The correct value for that col3 column for those corrupt rows appear in other rows with the same col1, col2, and col4 values where col3 is not 0. (col1 is the same value for all rows and just equals US.)

So to fix this, I just need to get all col3 rows that equal 0 and set those col3's to the value of the col3 where col3 does not equal 0 and where col1 = 'US' AND where col2 and col4 match.

Something like this:

UPDATE Table1 WHERE col1= 'US' AND col3 = '0' AND col2 = x AND col4 = y SET col3 = (SELECT col3 FROM Table1 WHERE col1= 'US' AND col2 = x AND col4 = y AND col3 != '0'

But how do I actually do that in MySQL as I just used x and y above as placeholders to explain the logic.

Upvotes: 0

Views: 169

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

In MySQL, you would do this with a JOIN:

UPDATE Table1 t1 JOIN
       (SELECT t1.*
        FROM Table1 t1
        WHERE col3 <> 0 AND col1 = 'US'
       ) tt1
       ON tt1.col2 = t1.col2 AND tt1.col4 = t1.col4
   SET t1.col3 = tt1.col3
   WHERE t1.col3 = 0;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Do a self join update:

UPDATE Table1 a
INNER JOIN Table1 b
    ON a.col2 = b.col2 AND
       a.col4 = b.col4 AND
       b.col3 <> 0
SET a.col3 = b.col3
WHERE
    a.col3 = 0 AND
    a.col1 = 'US'

Upvotes: 1

Related Questions