I did not wait a long time for the inspiration to write this next tip. The update statement can be confusing if you are not writing SQL on a regular basis. I will explain how to update a table with the records from another table. This is different than the more common single or multiple record update with a literal value. What developers often want to do is substitute the literal value with a query on the right side of the equal sign of the “SET” clause. This does not work. If the query resolves to a single value, it will update all the records with the same value.
In a case like this, it is good to return to basics. I will give you three examples that will help you remember how to do this on your own.
Single or Multi Record Update with a Literal
Most developers are familiar with the syntax that looks something like this:
UPDATE ADDRESS SET PROVINCE = ‘QC’ WHERE ADDRESS_ID = 65
This example there is a single table (ADDRESS) and we find the exact record (ID 65) using the “where” clause and the field PROVINCE is assigned the literal value QC. Probably at some point in your programming life you created a web page or script that did something like this.
In the example above, you updated 1 row. You could have changed the “where” clause to update more than one row by choosing criteria like the POSTAL_CODE beginning with H or G or J. (Those would be Quebec Postal Codes). Still pretty easy to understand. The selection of records that meet the criteria have changed and more will be updated.
Update a Field With Another Field in the Same Row
You could look at the table and realized you actually should have not put the literal QC in the PROVINCE field but in the PROVINCE_CODE field. You can update the value of one field in a table by assigning it the value of another field in the table. The command would look like this:
UPDATE ADDRESS SET PROVINCE_CODE = PROVINCE
That is the part you need to remember. The syntax to update every record using a value in the same table is the same syntax you used to update every or most records in one table with values from another table.
Updating a field with another field in the same row of the query
So now the part you want.
You need to create a query that combines the information in a single result. Then you assign the value in one field to another field. Let’s take an example where I have a PROVINCE_CODE field in the address table and I want to update the field PROVINCE in the same table. The lookup I will use is in another table called PROVINCE_LOOKUP that simply has two columns PROVINCE_CODE and PROVINCE. Example QC and Quebec.
This is what it looks like:
INNER JOIN PROVINCE_LOOKUP
ON ADDRESS.PROVINCE_CODE = PROVINCE_LOOKUP.PROVINCE_CODE
SET ADDRESS.PROVINCE = PROVINCE_LOOKUP.PROVINCE
So understanding these three kinds of updates always helps me remember the last one.
Have a nice day.