Mysql: Updating column based on another columns value

Question

I have about 300 records in my database that have invalid data in a column. The columns that are invalid reference a user_id instead of a id code (shown in table).

I'm trying to set the new_foreign_dealer_id = to the user_id inside the old_dealer_id_to_dealer column

So in the table, the user_id with the value 206 would search for 204 and replace 206's row column with 204's new_foreign_dealer_id

Users Table before:

+---------+-----------------------+-------------------------+
| user_id | new_foreign_dealer_id | old_dealer_id_to_delete |
+---------+-----------------------+-------------------------+
|     200 | 5                     | 02-000012               |
|     204 | 8                     | 02-000097               |
|     206 | 0 (invalid)           | 204 (referneces user_id |
+---------+-----------------------+-------------------------+

Users Table after:

+---------+-----------------------+-------------------------+
| user_id | new_foreign_dealer_id | old_dealer_id_to_delete |
+---------+-----------------------+-------------------------+
|     200 | 5                     | 02-000012               |
|     204 | 8                     | 02-000097               |
|     206 | 8                     | 204 (referneces user_id |
+---------+-----------------------+-------------------------+

This is the query I tried

UPDATE users SET dealer_id_foreign = dealer_id_foreign WHERE dealer_id = user_id

NOTE: The column names in my query are correct. The ASCII columns are for clarity purposes.

edit: ended up using PHP to achieve this. still interested in a sql answer

I think Alex's answer give you a SQL solution. Let know if the query is still not resolved. Otherwise, Please see: How to accept an answer for closure. You get points for it as well. Thanks :) — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 15, 2018 at 20:23

Alex · Accepted Answer · 2018-11-15 17:42:18Z

1

http://sqlfiddle.com/#!9/6e5a88/1

UPDATE my_table t
JOIN my_table new
ON t.old_dealer_id_to_delete = new.user_id
   AND t.new_foreign_dealer_id = 0
SET t.new_foreign_dealer_id = new.new_foreign_dealer_id;

answered Nov 15, 2018 at 17:42

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

1 Comment

Cat Named Dog Over a year ago

thanks! I can see that works. better than my PHP method

Nedko Dimitrov · Accepted Answer · 2018-11-15 17:36:33Z

0

Try this I haven't tested it,so please test it first

UPDATE users u
    JOIN users ucopy ON u.user_id = ucopy.user_id
SET dealer_id_foreign = dealer_id_foreign
WHERE dealer_id = user_id

answered Nov 15, 2018 at 17:36

Nedko Dimitrov

5,2413 gold badges31 silver badges31 bronze badges

Collectives™ on Stack Overflow

Mysql: Updating column based on another columns value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related