SQL 从另一个表中的另一列更新一列
在此之前我阅读了各种帖子。但他们似乎都不适合我。
正如标题所示,我正在尝试从另一个表中的列更新一列。我不记得以前遇到过此问题。
1.表:user_settings.contact_id,我想使用contacts.id进行更新where (user_settings.account_id == contacts_account_id)
2. 以前,联系人是通过 account_id 链接到用户帐户的。但是,现在我们想通过 contacts.id
将联系人链接到 user_settings
下面是我尝试过的一些示例,尽管它们都不起作用。我会对 A.) 为什么它们不起作用以及 B.) 我应该做什么感兴趣。
示例 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
示例 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
示例 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 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 :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
根据 MySQL 文档,要进行跨表更新,您不能使用联接(就像在其他数据库中一样),而是使用 where 子句:
http://dev.mysql.com/doc/refman/5.0/en/update.html
我认为这样的东西应该有效:
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: