SQL更新另一张表中的另一列中的一列 [英] 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.表:user_settings.contact_id，我想使用contacts.id where (user_settings.account_id == contacts_account_id)

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

2.以前，联系人是通过account_id链接到用户帐户的.但是，现在我们想通过contacts.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

下面是我尝试过的一些示例，尽管它们都没有起作用.我会对A.)为什么它们不起作用以及B.)我感兴趣.

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:

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 feel like my brain just shutdown on me and would appreciate any bumps to reboot it. Thanks :)

推荐答案

根据MySQL文档，要进行跨表更新，您不能使用联接(就像在其他数据库中一样)，而要使用where子句:

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

这篇关于SQL更新另一张表中的另一列中的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！