SQL Query to Update One Column Based on Data from

Question

I need to update a table column in one table with data from another based on whether a specific id matches.

Basically, I have the following schema:

TABLE accounts FIELD old_user_id

TABLE users FIELD old_user_id FIELD new_user_id

I need to loop through all old_user_id's in the accounts table checking them against the old_user_id field in the users table, and then take the new_user_id value in the users table and replace the old_user_id value in the accounts table.

Seems like a simple thing to do but as my SQL is not amazing I'm struggling with working this out.

Answer 1

Try This:

          UPDATE A 
          SET
                 A.old_user_id = U.new_user_id
          FROM Accounts A
          JOIN   Users U
          ON A.old_user_id = U.old_user_id