如何在SQL Server中使用SELECT在两个表之间进行UPDATE操作?

huangapple go评论65阅读模式
英文:

How can I UPDATE with a SELECT between two tables in SQL Server?

问题

这是我的代码:

UPDATE
    Table_A
SET
    Table_A.chat_id = Table_B.chat_id
FROM
    ac_messages AS Table_A
    INNER JOIN ac_contacts AS Table_B
        ON (Table_A.m_to = Table_B.users_id AND Table_A.m_from = Table_B.contacts_id) OR (Table_A.m_from = Table_B.users_id AND Table_A.m_to = Table_B.contacts_id)
WHERE
    (Table_A.m_to = Table_B.users_id AND Table_A.m_from = Table_B.contacts_id) OR (Table_A.m_from = Table_B.users_id AND Table_A.m_to = Table_B.contacts_id)

这里出现了语法错误:

#1064 - 在你的SQL语法中存在错误;请检查与你的MariaDB服务器版本相对应的手册,以了解正确使用的语法,位于第5行附近。

我的目标是将我为聊天定义的唯一ID传递给用户之间的消息。

英文:

This is my code:

UPDATE
    Table_A
SET
    Table_A.chat_id = Table_B.chat_id
FROM
    ac_messages AS Table_A
    INNER JOIN ac_contacts AS Table_B
        ON (Table_A.m_to = Table_B.users_id AND Table_A.m_from = Table_B.contacts_id) OR (Table_A.m_from = Table_B.users_id AND Table_A.m_to = Table_B.contacts_id)
WHERE
    (Table_A.m_to = Table_B.users_id AND Table_A.m_from = Table_B.contacts_id) OR (Table_A.m_from = Table_B.users_id AND Table_A.m_to = Table_B.contacts_id)

and this a sytanx error :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM
    ac_messages AS Table_A
    INNER JOIN ac_contacts AS Table_B
    ...' at line 5

My goal here is to pass on the unique ID I defined for chats to messages between users.

答案1

得分: 0

以下是翻译好的部分:

"FROM" 不属于 UPDATE 语句,请尝试以下操作:

UPDATE ac_messages INNER JOIN ac_contacts ON (ac_messages.m_to = 
    ac_contacts.users_id AND ac_messages.m_from = 
    ac_contacts.contacts_id) OR (ac_messages.m_from = ac_contacts.users_id 
    AND ac_messages.m_to = ac_contacts.contacts_id)
SET
    ac_messages.chat_id = ac_contacts.chat_id
WHERE
    (ac_messages.m_to = ac_contacts.users_id AND ac_messages.m_from = ac_contacts.contacts_id) OR (ac_messages.m_from = ac_contacts.users_id AND ac_messages.m_to = ac_contacts.contacts_id)

对于 INNER JOIN 中的 OR 语句,我不确定。

英文:

The FROM doesn't belong to un UPDATE, try the following

UPDATE ac_messages INNER JOIN ac_contacts ON (ac_messages.m_to = 
    ac_contacts.users_id AND ac_messages.m_from = 
    ac_contacts.contacts_id) OR (ac_messages.m_from = ac_contacts.users_id 
    AND ac_messages.m_to = ac_contacts.contacts_id)
SET
    ac_messages.chat_id = ac_contacts.chat_id
WHERE
    (ac_messages.m_to = ac_contacts.users_id AND ac_messages.m_from = ac_contacts.contacts_id) OR (ac_messages.m_from = ac_contacts.users_id AND ac_messages.m_to = ac_contacts.contacts_id)

Not sure about the OR in the INNER JOIN as well

huangapple
  • 本文由 发表于 2023年2月24日 14:11:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/75553145.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定