Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL update one column from another column in another table

I read various post's prior to this. but none of them seemed to work for me.

As the title suggests, I am trying to update one column from a column in another table. I don't recall having problems with this before..

1. Table: user_settings.contact_id, I want to update with contacts.id where (user_settings.account_id == contacts_account_id)

2. Previously Contacts were linked to user accounts via the account_id. However, now we want to link a contact to user_settings via contacts.id

Below are a few examples of what I have tried, though none of them have worked. I would be interested in A.) Why they don't work and B.) What should I do instead.

Example A:

UPDATE user_settings
SET user_settings.contact_id = contacts.id 
FROM user_settings 
INNER JOIN contacts ON user_settings.account_id = contacts.account_id

Example B:

UPDATE (SELECT A.contact_id id1, B.id id2
  FROM user_settings A, contacts B
  WHERE user_settings.account_id = contacts.account_id)
SET id1 = id2

Example C:

UPDATE user_settings
SET user_settings.contact_id = (SELECT id
  FROM contacts
  WHERE (user_settings.account_id = contacts.account_id)
WHERE EXISTS ( user_settings.account_id = contacts.account_id )

I feel like my brain just shutdown on me and would appreciate any bumps to reboot it. Thanks :)

like image 387
Kenny Cason Avatar asked Sep 29 '10 03:09

Kenny Cason


People also ask

How do you update a column in a table from another column in another table?

UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.

How can I update one table column from another table column in SQL Server?

SQL Server UPDATE JOIN syntaxFirst, specify the name of the table (t1) that you want to update in the UPDATE clause. Next, specify the new value for each column of the updated table. Then, again specify the table from which you want to update in the FROM clause.

How do you change a value from one table to another in SQL?

UPDATE syntax:UPDATE table_name SET column_name = value WHERE condition; To perform the above function, we can set the column name to be equal to the data present in the other table, and in the condition of the WHERE clause, we can match the ID. we can use the following command to create a database called geeks.


1 Answers

According to MySQL documentation, to do a cross table update, you can't use a join (like in other databases), but instead use a where clause:

http://dev.mysql.com/doc/refman/5.0/en/update.html

I think something like this should work:

UPDATE User_Settings, Contacts
    SET User_Settings.Contact_ID = Contacts.ID
    WHERE User_Settings.Account_ID = Contacts.Account_ID
like image 126
FlySwat Avatar answered Oct 09 '22 23:10

FlySwat