UPDATE语句

UPDATE语句

要修改表中的现有数据，请使用以下 UPDATE 语句语法：

UPDATE table_name SET c1 = v1, c2 = v2, ... cn = vn [WHERE condition]

在上面语法中，

首先，指定要从中更新数据的表的名称。

其次，指定要更新的列 c1 ， c2 ， ... ， cn 和值 v1 ， v2 ， ... vn 的列表。

第三，在 WHERE 子句中指定条件以选择更新的行。WHERE子句是可选的。 如果不指定 WHERE 子 句，则表中的所有行都将更新。 创建一个名为 taxes 的新表。

drop table if exists sales.taxes;

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 );

执行以下语句将数据插入 taxes 表：

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 INTO

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 INTO

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 INTO

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 INTO

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);

1. 更新所有行

以下语句更新 taxs 表中所有行的单个列：

UPDATE sales.taxes

SET updated_at = GETDATE();

在此示例中，语句将 updated_at 列中的值更改为 GETDATE() 函数返回的系统日期时间。 SQL Server发出以下消息：

(25 rows affected)

这表示有 25行记录已成功更新。

下面通过以下查询来验证更新结果：

从输出中可以清楚地看到， updated_at 列已使用当前日期值进行更新。

1. 更新多列示例

对于最高当地税率为 1% 的州，以下语句将最高当地税率提高 2% ，

以及平均税率提高 1% 。

UPDATE sales.taxes

SET max_local_tax_rate += 0.02,

avg_local_tax_rate += 0.01

WHERE

max_local_tax_rate = 0.01;

以下是SQL Server返回的消息：

(7 rows affected)

这意味着 7 个州的税收已经更新。

以上就是我的分享，新手上道，请多多指教。

如果有更好的方法或不懂得地方欢迎在评论区教导和提问喔！