Let's say you made a mistake when you inserted a record for the college information earlier in this chapter. You can update this information with the UPDATE clause. To update data in a table in MySQL, use the following syntax:
UPDATE lahmansbaseballdb.collegeplaying
SET
`playerID` = <{playerID: }>,
`schoolID` = <{schoolID: }>,
`yearID` = <{yearID: }>
WHERE ;
You can get this syntax by taking the following steps:
- Right-click on Tables in MySQL Workbench.
- Choose Send to SQL Editor.
- Choose Update Statement.
This places the UPDATE syntax for that table into a SQL query window.
You can execute this UPDATE query to update the record:
USE lahmansbaseballdb;
UPDATE collegeplaying
SET schoolID = 'sandiego', yearID = 2000
WHERE playerID = 'blaloha01';
The previous query updates the schoolID value to sandiego and the yearID value to 2000, which is shown in the following screenshot:
You can update one or more columns in an UPDATE statement. If you later discover that you wanted the yearID value to be 1999, you can execute the following query:
USE lahmansbaseballdb;
UPDATE collegeplaying
SET yearID = 1999
WHERE playerID = 'blaloha01';
The previous query sets the yearID value back to 1999, only for the rows where the playerID value is blaloha01.