phpmyadmin Question posted in
The official documentation can be found here.

Updating a column value from the value of the other table SQL – Phpmyadmin

I have a table chatmessages that has a chatId, incomingId, outgoingId column, and I have another table chatmembers which has a chatId, usersId column. Also another table chat that contains only the chatId, and the usersId initializer of the chat which is user1

enter image description here

enter image description here

Now, I want to copy the chatId from chatmembers to chatmessages where there are the same users inside it, example, the incomingId 83 and outgoingId 81 has the same chatId in chatmembers

enter image description here

I have this query but it doesnt work

UPDATE chatmessages SET chatId = 
(SELECT c.chatId FROM chat AS c INNER JOIN (SELECT chatId FROM chatmembers WHERE usersId = incomingId) AS uc ON c.chatId = uc.chatId 
INNER JOIN (SELECT chatId FROM chatmembers WHERE companyId = outgoingId) AS cc ON uc.chatId = cc.chatId)
Tags:

2

Answers


  1. 0

    If I understand correctly, you could use joins. The following uses MySQL syntax:

    update chatmessages cm join
       chatusers cuo
       on cuo.userid = cm.outgoingid join
       chatusers cui
       on cui.userid = cm.incomingid and
          cui.chatid = cuo.chatid
   set cm.chatid = cuo.chatid;
    Login or Signup to reply.
  2. 0

    you must write this query in array and next make execute,
    also use double quotes in

    (SELECT c.chatId FROM chat AS c 
INNER JOIN (SELECT chatId FROM chatmembers WHERE usersId = incomingId) AS uc ON c.chatId = uc.chatId 
INNER JOIN (SELECT chatId FROM chatmembers WHERE companyId = outgoingId)

    like this

    ("SELECT c.chatId FROM chat AS c 
INNER JOIN (SELECT chatId FROM chatmembers WHERE usersId = incomingId) AS uc ON c.chatId = uc.chatId 
INNER JOIN (SELECT chatId FROM chatmembers WHERE companyId = outgoingId")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search