You can use the query below in order to update records using SQL:
UPDATE table_name SET column_1 = value_1, column_2 = value_2, ... WHERE condition
Next, you’ll see how to apply the above query using a simple example.
The Example
Let’s suppose that you created a table, where:
- The table name is: product
- The table has 3 columns: product_id, product_name and price
The ‘product’ table also contains the following 6 records:
| product_id | product_name | price |
| 1 | Computer | 800 |
| 2 | TV | 1200 |
| 3 | Printer | 150 |
| 4 | Desk | 400 |
| 5 | Chair | 120 |
| 6 | Tablet | 300 |
Here is the query to create the product table:
CREATE TABLE product ( product_id int primary key, product_name nvarchar(50), price int )
And here is the query to insert the 6 records into the table:
INSERT INTO product (product_id, product_name, price) VALUES (1,'Computer',800), (2,'TV',1200), (3,'Printer',150), (4,'Desk',400), (5,'Chair',120), (6,'Tablet',300)
Let’s now see how to update the records in the table across the 2 scenarios below.
Scenario 1: Update a Single Record using SQL
To start with a simple example, let’s say that you want to update the last record in the ‘product’ table (this record represents the ‘Tablet’ product), where:
- The product_name will be updated to ‘Keyboard‘
- The price will be updated to 70
You can then run the query below to perform the update:
UPDATE product SET product_name = 'Keyboard', price = 70 WHERE product_id = 6
To verify that the record was updated, let’s run the following SELECT query:
SELECT * FROM product
As you can see, the product_name is now ‘Keyboard’ and the price is 70 for the last record (i.e., product_id = 6):
| product_id | product_name | price |
| 1 | Computer | 800 |
| 2 | TV | 1200 |
| 3 | Printer | 150 |
| 4 | Desk | 400 |
| 5 | Chair | 120 |
| 6 | Keyboard | 70 |
Scenario 2: Update an Entire Column
Lastly, let’s update the ‘price’ column by increasing the price by 100 across all the records:
UPDATE product SET price = price + 100
Run the SELECT query below in order to observe the results:
SELECT * FROM product
As you can see, the price increased by 100 across all the records in the ‘product’ table:
| product_id | product_name | price |
| 1 | Computer | 900 |
| 2 | TV | 1300 |
| 3 | Printer | 250 |
| 4 | Desk | 500 |
| 5 | Chair | 220 |
| 6 | Keyboard | 170 |
You may also want to learn how to: