SQL - Update

SQL UPDATE is the command used to update existing table rows with new data values. UPDATE is a very powerful command in the SQL world. It has the ability to update every single row in a database with the execution of only a single query. Due to UPDATE's supreme authority, it is in your best interest to always include a WHERE clause when working with UPDATE query statements. That way, you will not accidentally update more rows than you intend to.


Execute the following UPDATE command to update the customer orders table. Since we've provided a WHERE condition with this update command, this update will only modify rows that match the condition and in this case it happens to be order number 1 made by Tizag. This update should increase the quantity from 4 Pens to 6 Pens for Tizag's first order.

SQL Update Query:

USE mydatabase;

UPDATE orders
SET quantity = '6'
WHERE id = '1'

SQL Results:

(1 row(s) affected)

Let's verify our results by selecting this row from the orders table.

SQL Verification Query:

USE mydatabase;

SELECT *
FROM orders
WHERE id = '1'

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	6

The orders table now indicates that the customer Tizag will be ordering 6 Pens instead of 4. If the WHERE condition is removed from this statement, SQL would modify every row with the new quantity value of 6 instead of just the single row that meets the condition of id = "1". SQL UPDATE replaces data, much like overwriting a previously saved file on a computer hard drive. Once you click "Save," the old file is lost and replaced with the new file. Once an UPDATE command has been executed, the old data values are lost, being overwritten by the new value.

SQL - Update Incrementing a Value

In the previous example, an order quantity was updated from 4 to 6. Say what we really wanted to do was not necessarily change it to 6, but to add 2 to the original order quantity. Updating the order quantity from 4 to 6 might have gotten the job done in that scenario, but that solution doesn't scale well. In the long run, we wouldn't get very much "bang for our buck," as they say.
So, perhaps a better way to tackle the same problem would be to increment the existing value (add 2) rather than updating with a single, static value. So, instead of setting the quantity table column to a specific value of 6, we can send it the current table column value directly and then add 2 to that already existing value.

SQL Update Code:

USE mydatabase;

UPDATE orders
SET quantity = (quantity + 2)
WHERE id = '1'

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	8

Executing this update statement instead of the first update query is a huge timesaver. We no longer need to know the quantity of the order beforehand and we can add or subtract values from it in its current state. All we need to know is that we need to add 2 more to the quantity column to update the order correctly. This query is also more scalable, meaning we can update many rows at once. We will do so in the next example.

SQL - Update Multiple Rows

As mentioned earlier, removing the WHERE clause from any UPDATE command is generally not a good idea since doing so will result in SQL updating every row in the table. However, since the intention of this next example is to update multiple rows, let's go ahead and remove the WHERE clause from the above example.

SQL Update Multiple Rows:

USE mydatabase;

UPDATE orders
SET quantity = (quantity + 2)

SQL Results:

(6 row(s) affected)

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	10
2	Tia	2008-08-01 00:00:00.000	Stapler	3
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	5
6	Tia	2008-07-25 00:00:00.000	HP Printer	4

SQL Update Multiple Values

SQL UPDATE can also be utilized to change multiple column values at once. Once again, let's update the same order id (1) changing the quantity of products ordered. But let's also take it another step further, by changing the quantity only when the products are Hanging Files.

SQL Update Multiple Values:

USE mydatabase;

UPDATE orders
SET quantity = '11',
Product = 'Hanging Files'
WHERE id = '1'

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Hanging Files	11

The results show that we have successfully updated an order (order id 1). Notice that after the SET keyword, the column and value sets are listed with each column/value pair being separated with a comma (,).

Posted in: SQL