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 :)
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With