Previously we looked at finding data in a SQL database and The UPDATE statement is used to update records in a table. You can also use the UPDATE statement to update existing records in a table. When using the SQL UPDATE statement, we’ll also use the WHERE clause, as we used previously to constrain output of a SELECT statement. The WHERE locates the record(s) to be updated with syntax as follows:
UPDATE table
SET column=value,column=value,...
WHERE column=value;
The WHERE clause indicates the record(s) to update. I’ve forgotten to put it in in the past and updated every record of a database. That’s bad (unless you mean to do it). So let’s run a SELECT on our sample database from earlier, so we can see the “Customers” table from our first article:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
Now, let’s update the Address of the Apple record. To update the Address “spaceship” with the contents of “1 Infinite Loop” we’ll UPDATE the Customers table and SET the Address to equal
UPDATE Customers
SET Address='1 Infinite Loop'
WHERE Site='Apple';
The selection from the “Customers” table will now look like this:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook 1 Infinite Loop Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
You can also update multiple records by separating each item with a comma. For example, let’s say we wanted to update the Microsoft record to get the address correct:
UPDATE Customers
SET Address='One Microsoft Way',Zip="98052-7329"
WHERE Site='Microsoft';
Note that in the above example, I quoted Zip as I didn’t use an actual integer.
The selection from the “Customers” table will now look like this:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook 1 Infinite Loop Cupertino 95014 US
3 Microsoft Satya Nadella One Microsoft Way Redmond 98052-7329 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US