Siusic Dot Com

Yesterday I needed to write an update statement

to update records in a table that met conditions defined with two other tables. I always thought the select, update, insert and delete syntax are universal regardless of database vendors. So I wrote the script. It was like the following

Update C
Set C.status='Active'
From tableA A
Join tableB B on A.id=B.aid
Join tableC C on B.id=C.bid
Where A.id=1 or A.id=2 or A.id=3

This statement failed on MySQL and didn’t pass the syntax check. This statement is in (MSSQL) T-SQL syntax. In MySQL syntax the above update statement has to be rephrased as follow.

Update C
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Set C.status='Active'
Where A.id=1 or A.id=2 or A.id=3

You may thing the syntax difference between T-SQL and MySQL is little but in fact MySQL update statement can do more than T-SQL update statements. This is what surprised me. In a MySQL update statement you can update more than one table like this.

Update C
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Set C.status='Active',
A.status='Active'
Where A.id=1 or A.id=2 or A.id=3

There is no way you can do that in one MS SQL(T-SQL) statement. That’s one nice thing I saw in MySQL that MS SQL doesn’t have.

Related Posts:

MySQL Replication and MS SQL Log Shipping

A Fast Way to Update Large Amount of Data in SQL Server With Select Into Statement

MySQL Bug Errno 12 When Connecting MySQL Server Has Been Confirmed Fixed

MySQL Was Acquired By Sun For 1 Billion

Link MySQL Server Into MSSQL Server