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

Question

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.

Answer 1

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