MySQL vs MSSQL Updating Based On Two Tables

In most cases, MySQL code and MSSQL code is fairly similar. If you have a website running on MSSQL you can reconnect it to a matching database in MySQL without having to update the code.

But if you’re doing updates based on two tables, you will need to do some tweaking.

Here’s a sample block of code in MSSQL which updates a table based on the content of two tables.

update traffic_summary ts
set page_ct = vs.Expr1
from v_sitearticles vs
where ts.site_id = vs.site_id and
ts.month_id = 1 and ts.year_id = 2020

So in this case it’s the traffic summary table being updated, and it’s based on values out of the v_sitearticles view, based on month and year.

Note that all the “wheres” are done at once. That includes the join between the two tables as well as the selections from the tables.

But this code does NOT work in MySQL. MySQL uses a different format when doing updates of a table based on content of two different tables.

update traffic_summary ts
inner join v_sitearts vs on ts.site_id = vs.site_id
set ts.page_ct = vs.Expr1
where ts.Month_id =1 and ts.year_id = 2020

In MySQL you first give the join part of the statement, explaining how the tables join. Then after that you do the set comment. And then you do the selection criteria.

So they are fairly similar, but there are important differences. The MSSQL code will NOT run on MySQL. So if you do any of these kinds of updates, and are moving your system from MSSQL over to MySQL, be sure to watch for these situations.

Ask with any questions!

Updating ASP Classic Scripts from MSSQL to MySQL