Another post for the developers.
This whole post may be a result of me learning to write SQL queries in Oracle. But I found yesterday that in MySQL you can write an UPDATE statement that includes a JOIN. This was always off limits in Oracle. This means that you can update two tables at once and it also eliminates a lot of sub-quries so it is a lot faster. Here is a quick example.
Let’s say for example that you have an address with a zip code. You want to write a query to populate the longitude and latitude based on the zip code. You have a zipcodes table which contains the longitude and latitude for each zip code. So you could write the following statement.
update users
set latitude = (select latitude from zipcodes where zip_code=postal_code)
longitude = (select longitude from zipcodes where zip_code = postal_code);
We do two sub-queries for each record in the users table because we need to get two different columns from the zipcodes table.
But in MySQL because we can JOIN we can write this statement.
UPDATE users u
INNER JOIN
zipcodes z
ON z.zip_code = u.postal_code
SET u.latitude = z.latitude,
u.longitude = z.longitude;
This only requires one queriy no matter how many records are being updated. This can be substantial for larger tables.
