0

I would like to know the working of MySQL UPDATE table1 [,tables] SET col1=val1[,cols] query. Also, how is it different from UPDATE tables with JOIN query?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
SME
  • 23
  • 7
  • Is this a question about comma joins vs explicit joins? If so https://stackoverflow.com/questions/20138355/whats-the-difference-between-comma-separated-joins-and-join-on-syntax-in-mysql#:~:text=The%20comma%20operator%20is%20equivalent,clause%20into%20an%20ON%20clause. – P.Salmon Jul 22 '20 at 07:14
  • I read the document but was not able to understand how it is working. ' UPDATE t1,t2 SET t1.colName1=5, SET t2.colName1=3 ' How t1 and t2 is joined in the above query ? – SME Jul 22 '20 at 07:17
  • Tables t1 and t2 are comma joined (long unfashionable but still working) you should expand your reading to include comma joins see the link I posted and https://dev.mysql.com/doc/refman/8.0/en/select.html. BTW a comma join without a where condition is the same as a cross join. – P.Salmon Jul 22 '20 at 07:25
  • @P.Salmon Thank you for your explanation . I still have one more doubt. In Update query with comma separated join , the WHERE condition shall be true for same row multiple time. Will the same row be updated multiple times ? – SME Jul 22 '20 at 08:02
  • For example?... – P.Salmon Jul 22 '20 at 08:16

1 Answers1

1

Try this

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition
Albin C S
  • 340
  • 1
  • 5