Summary: In this walkthrough, you will learn how to use the SQL Server UPDATE statement to change existing data in a table.
To modify existing data in a table, use the following UPDATE statement:
UPDATE table_name SET c1 = v1, c2 = v2, … cn = vn [WHERE condition]Code language: SQL (Structured Query Language) (sql)
In this syntax
:First, specify the
- name of the table from which to update the data.
- Second, specify a list of columns c1, c2, …, cn, and values v1, v2, … vn to update.
- Third, specify the conditions of the WHERE clause to select the rows that are updated. The WHERE clause is optional. If you omit the WHERE clause, all rows in the table are updated.
SQL Server UPDATE Samples
First, create a new table named taxes for the demonstration.
CREATE TABLE sales.taxes ( tax_id INT PRIMARY KEY IDENTITY (1, 1), STATE VARCHAR (50) NOT NULL UNIQUE, state_tax_rate DEC (3, 2), avg_local_tax_rate DEC (3, 2), combined_rate AS state_tax_rate + avg_local_tax_rate, max_local_tax_rate DEC (3, 2), updated_at datetime ); Code language: SQL (Structured Query Language) (sql)
Second, run the following statements to insert data into the tax table:
INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Alabama’,0.04,0.05,0.07); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Alaska’,0,0.01,0.07); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Arizona’,0.05,0.02,0.05); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Arkansas’,0.06,0.02,0.05); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘California’,0.07,0.01,0.02); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Colorado’,0.02,0.04,0.08); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Connecticut’,0.06,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Delaware’,0,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Florida’,0.06,0,0.02); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Georgia’,0.04,0.03,0.04); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Hawaii’,0.04,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Idaho’,0.06,0,0.03); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Illinois’,0.06,0.02,0.04); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Indiana’,0.07,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Iowa’,0.06,0,0.01); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Kansas’,0.06,0.02,0.04); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Kentucky’,0.06,0,0); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Louisiana’,0.05,0.04,0.07); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Maine’,0.05,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Maryland’,0.06,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Massachusetts’,0.06,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Michigan’,0.06,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Minnesota’,0.06,0,0.01); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Mississippi’,0.07,0,0.01); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Missouri’,0.04,0.03,0.05); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Montana’,0,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Nebraska’,0.05,0.01,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Nevada’,0.06,0.01,0.01); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘New Hampshire’,0,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘New Jersey’,0.06,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘New Mexico’,0.05,0.02,0.03); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘New York’,0.04,0.04,0.04); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘North Carolina’,0.04,0.02,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘North Dakota’,0.05,0.01,0.03); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Ohio’,0.05,0.01,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Oklahoma’,0.04,0.04,0.06); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Oregon’,0,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Pennsylvania’,0.06,0,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Rhode Island’,0.07,0,0); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘South Carolina’,0.06,0.01,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘South Dakota’,0.04,0.01,0.04); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Tennessee’,0.07,0.02,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Texas’,0.06,0.01,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Utah’,0.05,0,0.02); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Vermont’,0.06,0,0.01); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Virginia’,0.05,0,0); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Washington’,0.06,0.02,0.03); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘West Virginia’,0.06,0,0.01); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Wisconsin’,0.05,0,0.01); INSERT INTO sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘Wyoming’,0.04,0.01,0.02); INSERT IN sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate) VALUES(‘D.C.’,0.05,0,0); Code language: JavaScript (javascript)
1) Example of updating
a single column in all rows
The following statement updates a single column for all rows in the tax table:
UPDATE sales.taxes SET updated_at = GETDATE(); Code language: SQL (Structured Query Language) (sql)
In this example, the statement changed the values in the updated_at column to the system date and time returned by the GETDATE() function.
SQL
Server issued the following message
:(51 rows affected) Code language: SQL (Structured Query Language) (sql)
It means that 51 rows have been successfully updated
.
Let’s verify the update:
SELECT * FROM sales.taxes; Code language: SQL (Structured Query Language) (sql)
Here is the partial result:
As you can clearly see from the output, the updated_at column has been updated with the current date value.
2) Update the multi-column example
The following statement increases the maximum local tax rate by 2% and the average local tax rate
by 1% for states that have the maximum local tax rate of 1%.
UPDATE SALES. TAXES ESTABLISH max_local_tax_rate += 0.02, avg_local_tax_rate += 0.01 WHERE max_local_tax_rate = 0.01; Code language: SQL (Structured Query Language) (sql)
Here is the message returned by
the SQL Server:(7 rows affected)Code language: SQL (Structured Query Language) (sql)
It means that the taxes of 7 states have been updated
.
In this walkthrough, you learned how to use the SQL Server UPDATE statement to modify existing data in a table.
