PostgreSQL/Insert Delete Update/Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:14, 26 мая 2010
Содержание
- 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
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=#
Update a single row
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=#
Update in a slice
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=#
Update two columns in one single statement
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=#
Updating entire columns
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=#
Updating Several Columns
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=#
Using expression in update statement
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=#
Using UPDATE with several Employees
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=#
Using user defined function to update table data
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=#