20

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 :)

linuxbuild
  • 15,843
  • 6
  • 60
  • 87
Kenny Cason
  • 12,109
  • 11
  • 47
  • 72

2 Answers2

39

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
FlySwat
  • 172,459
  • 74
  • 246
  • 311
  • Thanks I definitely just mixed up my databases. As soon as you typed that gears started churning. – Kenny Cason Sep 29 '10 at 03:39
  • your answer was exactly what i was looking for. if you change the table and column names to match mine, it may make the answer better match the question. Thanks again! – Kenny Cason Sep 29 '10 at 03:43
-1
Update tabelName Set SanctionLoad=SanctionLoad Where ConnectionId=ConnectionID
go
update tabelName  Set meterreading=meterreading where connectionid=connectionid
go
update tabelName  set customername=setcustomername where customerid=customerid
Matt
  • 74,352
  • 26
  • 153
  • 180
Rajesh C
  • 9
  • Would be nice to have some context, e.g. explaining the go-keyword. I realize the answer is now 8 years old, but still. – Sebastian Jun 21 '21 at 11:14