MySQL SQL 从另一个表中的另一列更新一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3818512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL update one column from another column in another table
提问by Kenny Cason
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)
1.表：user_settings.contact_id，我想用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_settingsvia contacts.id
2.以前，联系人通过 account_id 链接到用户帐户。但是，现在我们想将联系人链接到user_settingsviacontacts.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.
以下是我尝试过的一些示例，尽管它们都没有奏效。我会对 A.) 为什么它们不起作用和 B.) 我应该做什么感兴趣。
Example A:
示例 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:
示例 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:
示例 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 :)
我觉得我的大脑只是关闭了我，并希望重新启动它。谢谢 ：）
回答by FlySwat
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:
根据 MySQL 文档，要进行交叉表更新，您不能使用连接（就像在其他数据库中一样），而是使用 where 子句：
http://dev.mysql.com/doc/refman/5.0/en/update.html
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
回答by Rajesh C
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
