Oracle更新錶資料

在本教學中將學習如何使用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語句更改表中的現有值。