Update Table Records using SQL

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: