SELECT<\/code> statement after executing the script above:<\/p>\nSELECT * FROM airplanes;<\/pre>\nFigure 1 shows part of the results returned by the SELECT<\/code> statement. Notice that the wingspan<\/code> values are now all whole numbers.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-41.png\")
Figure 1. Rounding the values in the wingspan<\/code> column<\/p>\nIn some cases, you might want to update multiple columns at the same time. For this, you need to add the additional column assignments, separating them with commas. For example, the following UPDATE<\/code> statement modifies both the wingspan<\/code> and plane_length<\/code> columns:<\/p>\nSET SQL_SAFE_UPDATES = 0;\r\n\r\nUPDATE airplanes\r\nSET wingspan = ROUND(wingspan), plane_length = ROUND(plane_length);\r\n\r\nSET SQL_SAFE_UPDATES = 1;<\/pre>\nBoth column assignments work the same way. You\u2019re simply rounding the column values to whole numbers. If you were to query the airplanes<\/code> table after running the UPDATE<\/code> statement, your results would look similar to those shown in Figure 2.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-42.png\")
Figure 2. Rounding the values in the wingspan<\/code> and plane_length<\/code> columns<\/p>\nBy using the UPDATE<\/code> and SET<\/code> clauses, you can quickly update all of a column\u2019s values. Just be careful if taking this approach. It\u2019s all too easy to mess things up in a big way.<\/p>\nAdding a WHERE<\/strong><\/code> clause to your UPDATE<\/strong><\/code> statement<\/h2>\nMost of your UPDATE<\/code> statements will likely include a WHERE<\/code> clause to help you better target the data that you want to modify. The WHERE<\/code> clause specifies one or more conditions that narrow down the rows to be updated. For example, the following statement includes a WHERE<\/code> clause that limits the updates to rows with a manufacturer_id<\/code> value of 1008<\/code>:<\/p>\nUPDATE airplanes\r\nSET engine_type = 'piston (adg-i)' \r\nWHERE manufacturer_id = 1008;\r\n\r\nSELECT *\r\nFROM airplanes\r\nWHERE manufacturer_id = 1008;<\/pre>\nThe SET<\/code> clause in this statement sets the engine_type<\/code> value to piston<\/code> (adg-i)<\/code> for the targeted rows. The results from executing the statements should look similar to Figure 3.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-43.png\")
Figure 3. Limiting your update to specific rows<\/p>\n
Note<\/strong>: In case you\u2019re wondering, the adg-i<\/code> value is a reference to Airplane Design Group (ADG) classifications, a system used to categorize aircraft by dividing them into six groups based on their wingspans and tail heights. The lowercase i<\/code> indicates that the planes in this example are in Group I. (I realize that you\u2019d probably want to add a column for the ADG groups. The approach I took here was meant only to demonstrate these concepts.)<\/p>\nThat said, it turns out that the B.206 airplane should actually be in Group II, which means you need to update that record without updating the others. Fortunately, you can define multiple conditions in your WHERE<\/code> clause to help narrow down the rows. In the following example, the WHERE<\/code> clause includes two conditions, one based on the manufacturer_id<\/code> column and the other on the plane<\/code> column:<\/p>\nUPDATE airplanes\r\nSET engine_type = 'piston (adg-ii)'\r\nWHERE manufacturer_id = 1008 AND plane = 'B.206';<\/pre>\nAs in the previous example, the WHERE<\/code> clause limits the updates to rows with a manufacturer_id<\/code> value of 1008<\/code>. However, the clause also specifies that the plane<\/code> value must equal B.206<\/code>. The two conditions are linked together by the AND<\/code> logical operator, which means that both conditions must evaluate to true for the row to be updated.<\/p>\nAfter you run the UPDATE<\/code> statement, you can retrieve the same rows as before. Your results should look similar to those shown in Figure 4. Notice that the B.206 aircraft is now shown as a Group II plane.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-44.png\")
Figure 4. Limiting your update to one specific row<\/p>\n
You can make your WHERE<\/code> clause as detailed as necessary to ensure that you\u2019re updating the target rows and no other rows. The key is to use your logical operators correctly to ensure that your conditional logic is accurate.<\/p>\nWorking with column values<\/h2>\n
In the first example in this article, you saw how to use the ROUND<\/code> system function to round values in the airplanes<\/code> table. When you update a column in this way, MySQL uses the column\u2019s current value to create a new value. The ability to use the current value makes it possible to build on that value in ways that go beyond simply applying a function. For example, the following UPDATE<\/code> statement adds 3 to the wingspan<\/code> value and 5 to the plane_length<\/code> value:<\/p>\nUUPDATE airplanes \r\nSET wingspan = wingspan + 3, plane_length = plane_length + 5 \r\nWHERE plane_id = 344;<\/pre>\nIn this case, I used the plane_id<\/code> value 344<\/code> in the WHERE<\/code> clause, which I had to look up in the table. However, you might want to use a different method for finding this value, such as retrieving it through a subquery, a topic I plan to cover later in this series.<\/p>\nAfter your run this statement, you can query the airplanes<\/code> table to verify the results, which should look similar to those shown in Figure 5.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-45.png\")
Figure 5. Increasing values in the wingspan<\/code> and plane_length<\/code> columns<\/p>\nIf you compare Figure 5 to Figure 4, you\u2019ll see that the row with a plane_id<\/code> value of 344<\/code> has been updated. You might have also noticed that MySQL automatically updated the parking_area<\/code> column, which is a generated column that multiples the wingspan<\/code> and plane_length<\/code> values.<\/p>\nIf you try to update a column with the same value it already has, MySQL is smart enough to realize the values are the same and does not change the original value. This approach could potentially reduce unnecessary overhead and minimize the impact on concurrent operations that might be trying to retrieve or modify that value at the same time. MySQL is also smart enough to recognize when you try to insert an unacceptable value into a column. For instance, the following UPDATE<\/code> statement attempts to change the engine_type<\/code> column to NULL<\/code>:<\/p>\nUPDATE airplanes SET engine_type = NULL WHERE plane_id = 344;<\/pre>\nBecause the column is defined as NOT<\/code> NULL<\/code>, the UPDATE<\/code> statement will fail and generate the following error:<\/p>\nError Code: 1048. Column 'engine_type' cannot be null<\/code><\/p>\nYou\u2019ll also receive an error if you attempt to update a column to a value with an incorrect data type. For example, the following UPDATE<\/code> statement attempts to update the max_weight<\/code> column to the string value unknown<\/code>:<\/p>\nUPDATE airplanes\r\nSET max_weight = 'unknown'\r\nWHERE plane_id = 344;<\/pre>\nNot surprisingly, this statement will also fail because the max_weight<\/code> column is defined with the MEDIUMINT<\/code> data type. Rather than update the value, MySQL returns the following error:<\/p>\nError Code: 1366. Incorrect integer value: 'unknown' for column 'max_weight' at row 1<\/code><\/p>\nAs with inserting data, updating data requires that you\u2019re familiar with the target columns whose values you\u2019re trying to modify. It\u2019s not enough just to know the data type. You must also understand how the data type is defined. For example, if you try to update the icao_code<\/code> column with the string abcdef<\/code>, you\u2019ll generate an error because the column is defined as CHAR(4)<\/code>.<\/p>\nUpdating foreign key columns in MySQL tables<\/h2>\n
There might be times when you want to update a value in a foreign key column. This can be tricky, however, because MySQL performs foreign key checks. For example, suppose you want to modify the manufacturer_id<\/code> column in the airplanes<\/code> table:<\/p>\nUPDATE airplanes \r\nSET manufacturer_id = 2001\r\nWHERE manufacturer_id = 1008;<\/pre>\nNot surprisingly, MySQL will balk when you try to run this statement and will instead return the following error (unless you\u2019ve included the IGNORE<\/code> keyword:<\/p>\nError Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))<\/code><\/p>\nYou cannot update a foreign key to a value that does not exist in the referenced column. You must first make the necessary changes to the parent table. However, this too can be tricky. For example, you might try to modify the manufacturer_id<\/code> value in the manufacturers<\/code> table:<\/p>\nUPDATE manufacturers \r\nSET manufacturer_id = 2001\r\nWHERE manufacturer_id = 1008;<\/pre>\nUnfortunately, this too will cause MySQL to generate an error because you cannot update a value that\u2019s being referenced by a foreign key:<\/p>\n
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))<\/code><\/p>\nYou can get around these issues by temporarily disabling foreign key checks within your session (or by setting the CASCADE<\/code> option on the foreign key, something I\u2019ll be discussing in a later article). To achieve this, set the foreign_key_checks<\/code> system variable to 0<\/code> before running the UPDATE<\/code> statements, and then set it back to 1<\/code> after running the statements:<\/p>\nSET foreign_key_checks = 0;\r\n\r\nUPDATE manufacturers \r\nSET manufacturer_id = 2001\r\nWHERE manufacturer_id = 1008;\r\n\r\nUPDATE airplanes \r\nSET manufacturer_id = 2001\r\nWHERE manufacturer_id = 1008;\r\n\r\nSET foreign_key_checks = 1;<\/pre>\nIn this way, you can update the manufacturer_id<\/code> values in both tables without generating any foreign key errors. As a reminder, avoid using the GLOBAL<\/code> option in your SET<\/code> statement. If you turn off foreign key checks at a global level, you\u2019re putting the integrity of your data at risk.<\/p>\nAfter you run these statements, you can query the manufacturers<\/code> table to verify your changes:<\/p>\n