SQL 从另一个表中的另一列更新一列

发布于 2024-09-25 23:51:12 字数 1060 浏览 1 评论 0原文

在此之前我阅读了各种帖子。但他们似乎都不适合我。

正如标题所示，我正在尝试从另一个表中的列更新一列。我不记得以前遇到过此问题。

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故事与诗 2024-10-02 23:51:12

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

http://dev.mysql.com/doc/refman/5.0/en/update.html

我认为这样的东西应该有效：

UPDATE User_Settings, Contacts
    SET User_Settings.Contact_ID = Contacts.ID
    WHERE User_Settings.Account_ID = Contacts.Account_ID

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

回复收藏 0 原文

顾挽 2024-10-02 23:51:12

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

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

回复收藏 0 原文

~没有更多了~