Oracle更新表数据

作者：--    发布时间：2019-11-20

在本教程中将学习如何使用oracle update语句来更改表中的已存在值。

oracle update语句简介

要更改表中已存在的值，请使用以下oracle update语句：

update
    table_name
set
    column1 = value1,
    column2 = value2,
    column3 = value3,
    ...
where
    condition;

oracle update示例

下面让我们来创建一个包含示例数据的新表。

首先，下面的create table语句创建一个名为parts的新表：

-- oracle 12c的写法
create table parts (
  part_id number generated by default as identity,
  part_name varchar(50) not null,
  lead_time number(2,0) not null,
  cost number(9,2) not null,
  status number(1,0) not null,
  primary key (part_id)
);

-- oracle 11g的写法
drop sequence parts_seq;

create sequence parts_seq
 increment by 1
 start with 1
 maxvalue 9999999999
 nocache;

drop table parts;
create table parts (
  part_id number,
  part_name varchar(50) not null,
  lead_time number(2,0) not null,
  cost number(9,2) not null,
  status number(1,0) not null,
  primary key (part_id)
);

其次，以下insert语句将示例数据添加到parts表中：

-- oracle 11g的插入数据写法
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'sed dictum',5,134,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'tristique neque',3,62,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'dolor quam,',16,82,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'nec, diam.',41,10,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'vitae erat',22,116,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'parturient montes,',32,169,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'metus. in',45,88,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'at, velit.',31,182,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'nonummy ultricies',7,146,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'a, dui.',38,116,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'arcu et',37,72,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'sapien. cras',40,197,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'et malesuada',24,46,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'mauris id',4,153,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'eleifend egestas.',2,146,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'cursus. nunc',9,194,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'vivamus sit',37,93,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'ac orci.',35,134,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'arcu. aliquam',36,154,0);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'at auctor',32,56,1);
insert into parts (part_id,part_name,lead_time,cost,status) values (parts_seq.nextval,'purus, accumsan',33,12,1);

oracle 12c 插入数据语句 -

-- oracle 12c写法
insert into parts (part_name,lead_time,cost,status) values ('sed dictum',5,134,0);
insert into parts (part_name,lead_time,cost,status) values ('tristique neque',3,62,1);
insert into parts (part_name,lead_time,cost,status) values ('dolor quam,',16,82,1);
insert into parts (part_name,lead_time,cost,status) values ('nec, diam.',41,10,1);
insert into parts (part_name,lead_time,cost,status) values ('vitae erat',22,116,0);
insert into parts (part_name,lead_time,cost,status) values ('parturient montes,',32,169,1);
insert into parts (part_name,lead_time,cost,status) values ('metus. in',45,88,1);
insert into parts (part_name,lead_time,cost,status) values ('at, velit.',31,182,0);
insert into parts (part_name,lead_time,cost,status) values ('nonummy ultricies',7,146,0);
insert into parts (part_name,lead_time,cost,status) values ('a, dui.',38,116,0);
insert into parts (part_name,lead_time,cost,status) values ('arcu et',37,72,1);
insert into parts (part_name,lead_time,cost,status) values ('sapien. cras',40,197,1);
insert into parts (part_name,lead_time,cost,status) values ('et malesuada',24,46,0);
insert into parts (part_name,lead_time,cost,status) values ('mauris id',4,153,1);
insert into parts (part_name,lead_time,cost,status) values ('eleifend egestas.',2,146,0);
insert into parts (part_name,lead_time,cost,status) values ('cursus. nunc',9,194,1);
insert into parts (part_name,lead_time,cost,status) values ('vivamus sit',37,93,0);
insert into parts (part_name,lead_time,cost,status) values ('ac orci.',35,134,0);
insert into parts (part_name,lead_time,cost,status) values ('arcu. aliquam',36,154,0);
insert into parts (part_name,lead_time,cost,status) values ('at auctor',32,56,1);
insert into parts (part_name,lead_time,cost,status) values ('purus, accumsan',33,12,1);

现在，查询上面创建的parts表中的数据 -

1. oracle update - 更新单个行的一列

以下update语句更改id为3的零件的成本：

update
    parts
set
    cost = 130
where
    part_id = 3;

要验证更新，请使用以下查询：

select
    *
from
    parts
where
    part_id = 3;

执行上面查询语句，得到以下结果 -

2. oracle update - 更新单个行的多个列

以下语句更新id为6的零件的前置时间，成本和状态。

update
    parts
set
    lead_time = 30,
    cost = 120,
    status = 1
where
    part_id = 6;

要验证更新，请使用以下查询：

select
    *
from
    parts
where
    part_id = 6;

执行上面查询语句，得到以下结果 -

3. oracle update - 更新多行示例

以下声明增加了parts表中所有零件的成本5％：

update
    parts
set
    cost = cost * 1.05;

执行上面查询语句后，再次查询每个零件的成本 -

在本教程中，您已学习如何使用oracle update语句更改表中的现有值。