PostgreSQL/Insert Delete Update/Update
Содержание
- 1 A simple UPDATE: change one cell
- 2 Update a single row
- 3 Update in a slice
- 4 Update two columns in one single statement
- 5 Updating entire columns
- 6 Updating Several Columns
- 7 Using expression in update statement
- 8 Using UPDATE with several Employees
- 9 Using user defined function to update table data
A simple UPDATE: change one cell
<source lang="sql">
postgres=# postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# -- A simple UPDATE postgres=# postgres=# SELECT name FROM employee WHERE id = 1;
name
Jason
(1 row) postgres=# postgres=# UPDATE employee SET name = "newName" WHERE id = 1; UPDATE 1 postgres=# postgres=# SELECT name FROM employee WHERE id = 1;
name
newName
(1 row) postgres=# postgres=# postgres=# postgres=# drop table employee; DROP TABLE postgres=#
</source>
Update a single row
<source lang="sql">
postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# postgres=# UPDATE employee SET id = 116 WHERE id = 1; UPDATE 1 postgres=# SELECT * FROM employee WHERE id = 1;
id | name | salary | start_date | city | region
+------+--------+------------+------+--------
(0 rows) postgres=# postgres=# postgres=# postgres=# postgres=# postgres=# drop table employee; DROP TABLE postgres=#
</source>
Update in a slice
<source lang="sql">
postgres=# postgres=# postgres=# CREATE TABLE sal_emp ( postgres(# name text, postgres(# pay_by_quarter integer[], postgres(# schedule text[][] postgres(# ); CREATE TABLE postgres=# postgres=# INSERT INTO sal_emp postgres-# VALUES ("Bill", postgres(# ARRAY[10000, 10000, 10000, 10000], postgres(# ARRAY[["meeting", "lunch"], ["training", "presentation"]]); INSERT 0 1 postgres=# postgres=# INSERT INTO sal_emp postgres-# VALUES ("Carol", postgres(# ARRAY[20000, 25000, 25000, 25000], postgres(# ARRAY[["breakfast", "consulting"], ["meeting", "lunch"]]); INSERT 0 1 postgres=# postgres=# SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}} Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows) postgres=# postgres=# -- Update in a slice: postgres=# postgres=# UPDATE sal_emp SET pay_by_quarter[1:2] = "{27000,27000}" WHERE name = "Carol"; UPDATE 1 postgres=# postgres=# select * from sal_emp;
name | pay_by_quarter | schedule
+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}} Carol | {27000,27000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows) postgres=# postgres=# drop table sal_emp; DROP TABLE postgres=# postgres=#
</source>
Update two columns in one single statement
<source lang="sql">
postgres=# postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# postgres=# INSERT INTO products (product_no, name, price) VALUES (1, "Java", 1234); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (2, "SQL Server", 3421); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (3, "Oracle", 7623); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (4, "DB2", 9874); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (5, "Access", 5); INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+------------+-------
1 | Java | 1234 2 | SQL Server | 3421 3 | Oracle | 7623 4 | DB2 | 9874 5 | Access | 5
(5 rows) postgres=# postgres=# UPDATE products SET product_no = 999, name = "new Name", price = 1 WHERE price > 0; UPDATE 5 postgres=# postgres=# select * from products;
product_no | name | price
+----------+-------
999 | new Name | 1 999 | new Name | 1 999 | new Name | 1 999 | new Name | 1 999 | new Name | 1
(5 rows) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=#
</source>
Updating entire columns
<source lang="sql">
postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# -- Updating entire columns postgres=# postgres=# SELECT * FROM employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# UPDATE employee SET salary = (salary * 2); UPDATE 9 postgres=# postgres=# SELECT * FROM employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 80840 | 1994-02-01 | New York | W 2 | Robert | 28840 | 1995-01-02 | Vancouver | N 3 | Celia | 48040 | 1996-12-03 | Toronto | W 4 | Linda | 81240 | 1997-11-04 | New York | N 5 | David | 160052 | 1998-10-05 | Vancouver | W 6 | James | 140120 | 1999-09-06 | Toronto | N 7 | Alison | 181240 | 2000-08-07 | New York | W 8 | Chris | 52040 | 2001-07-08 | Vancouver | N 9 | Mary | 120040 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# postgres=# postgres=# drop table employee; DROP TABLE postgres=#
</source>
Updating Several Columns
<source lang="sql">
postgres=# postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(40), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(100), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# -- Updating Several Columns postgres=# postgres=# UPDATE employee postgres-# SET name = "O\"Reilly", postgres-# city = "newCity" || " and new town" postgres-# WHERE id = 1; UPDATE 1 postgres=# postgres=# SELECT * FROM employee WHERE id = 1;
id | name | salary | start_date | city | region
+----------+--------+------------+----------------------+--------
1 | O"Reilly | 40420 | 1994-02-01 | newCity and new town | W
(1 row) postgres=# postgres=# postgres=# postgres=# drop table employee; DROP TABLE postgres=#
</source>
Using expression in update statement
<source lang="sql">
postgres=# postgres=# CREATE TABLE products ( postgres(# product_no integer, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# postgres=# INSERT INTO products (product_no, name, price) VALUES (1, "Java", 1234); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (2, "SQL Server", 3421); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (3, "Oracle", 7623); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (4, "DB2", 9874); INSERT 0 1 postgres=# INSERT INTO products (product_no, name, price) VALUES (5, "Access", 5); INSERT 0 1 postgres=# postgres=# postgres=# select * from products;
product_no | name | price
+------------+-------
1 | Java | 1234 2 | SQL Server | 3421 3 | Oracle | 7623 4 | DB2 | 9874 5 | Access | 5
(5 rows) postgres=# postgres=# UPDATE products SET price = price * 1.10; UPDATE 5 postgres=# postgres=# select * from products;
product_no | name | price
+------------+----------
1 | Java | 1357.40 2 | SQL Server | 3763.10 3 | Oracle | 8385.30 4 | DB2 | 10861.40 5 | Access | 5.50
(5 rows) postgres=# postgres=# drop table products; DROP TABLE postgres=# postgres=#
</source>
Using UPDATE with several Employees
<source lang="sql">
postgres=# postgres=# postgres=# create table job( postgres(# ID int, postgres(# title varchar (10)); CREATE TABLE postgres=# postgres=# postgres=# insert into job(ID, title) values(1,"Developer"); INSERT 0 1 postgres=# insert into job(ID, title) values(2,"Tester"); INSERT 0 1 postgres=# insert into job(ID, title) values(3,"Designer"); INSERT 0 1 postgres=# insert into job(ID, title) values(4,"Programmer"); INSERT 0 1 postgres=# postgres=# select * from job;
id | title
+------------
1 | Developer 2 | Tester 3 | Designer 4 | Programmer
(4 rows) postgres=# postgres=# postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W 2 | Robert | 14420 | 1995-01-02 | Vancouver | N 3 | Celia | 24020 | 1996-12-03 | Toronto | W 4 | Linda | 40620 | 1997-11-04 | New York | N 5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows) postgres=# postgres=# -- Using UPDATE with several Employees postgres=# postgres=# UPDATE employee postgres-# SET salary = salary + 100 postgres-# FROM job postgres-# WHERE job.id = employee.id; UPDATE 4 postgres=# postgres=# select * from employee;
id | name | salary | start_date | city | region
+--------+--------+------------+-----------+--------
5 | David | 80026 | 1998-10-05 | Vancouver | W 6 | James | 70060 | 1999-09-06 | Toronto | N 7 | Alison | 90620 | 2000-08-07 | New York | W 8 | Chris | 26020 | 2001-07-08 | Vancouver | N 9 | Mary | 60020 | 2002-06-09 | Toronto | W 1 | Jason | 40520 | 1994-02-01 | New York | W 2 | Robert | 14520 | 1995-01-02 | Vancouver | N 3 | Celia | 24120 | 1996-12-03 | Toronto | W 4 | Linda | 40720 | 1997-11-04 | New York | N
(9 rows) postgres=# postgres=# postgres=# drop table job; DROP TABLE postgres=# drop table employee; DROP TABLE postgres=#
</source>
Using user defined function to update table data
<source lang="sql">
postgres=# CREATE TABLE emp ( postgres(# name text, postgres(# salary numeric, postgres(# age integer, postgres(# cubicle point postgres(# ); CREATE TABLE postgres=# postgres=# insert into emp (salary) values(100); INSERT 0 1 postgres=# insert into emp (salary) values(200); INSERT 0 1 postgres=# insert into emp (salary) values(300); INSERT 0 1 postgres=# postgres=# CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$ postgres$# SELECT $1.salary * 2 AS salary; postgres$# $$ LANGUAGE SQL; CREATE FUNCTION postgres=# postgres=# SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream postgres-# FROM emp;
name | dream
+-------
| 220.0 | 440.0 | 660.0
(3 rows) postgres=# postgres=# drop function double_salary(emp); DROP FUNCTION postgres=# drop table emp; DROP TABLE postgres=# postgres=#
</source>