Pfthb

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

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;

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