SQL 如何在PostgreSQL中的表的新添加列的行中插入值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13448993/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert values into the rows of a newly added column of a table in PostgreSQL
提问by Barbara Lokes
I imported a personal geodatabase into PostgreSQL. For one of my tables, I added a new column. This column is empty and I want to add in/insert values into this column so that all its rows will be populated. When I used:
我将个人地理数据库导入 PostgreSQL。对于我的一个表，我添加了一个新列。此列为空，我想在此列中添加/插入值，以便填充其所有行。当我使用：
INSERT INTO district (province_id) VALUES
('13'), ('13'), ('13'), ('13'), ('13'), ('13'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('19'), ('19');
The result is such that new rows were created, so instead of having 17 rows, I ended up having 34 rows. When I try to use UPDATE...Set...=... Where...., the query ran successfully, however, no row was affected. My question now is: how can I add the values for each of the rows in the newly created column such that no new rows will be created as a result?
结果是创建了新行，所以我最终有 34 行而不是 17 行。当我尝试使用 UPDATE...Set...=...Where.... 时，查询成功运行，但是，没有任何行受到影响。我现在的问题是：如何为新创建的列中的每一行添加值，从而不会创建新行？
Thanks in advance and any help would be very much appreciated.
提前致谢，任何帮助将不胜感激。
Barbaara
芭芭拉
回答by Igor Romanchenko
First - in the table "district" you must choose a column, that is unique for every record in "district" (a primary key. I guess it is called "district_id").
首先 - 在“区”表中，您必须选择一列，该列对于“区”中的每条记录都是唯一的（主键。我猜它被称为“区 ID”）。
Then for every "district_id" you need to set the "province_id" :
然后对于每个“district_id”，您需要设置“province_id”：
UPDATE district
SET province_id = 13 -- put your province_id value here
WHERE district_id = 1234; -- put the key value here
It will search for a record in "district" with "district_id" = 1234 and SET its "province_id" to 13.
它将在“district”中搜索“district_id”=1234的记录并将其“province_id”设置为13。
Next you must write such UPDATE's for every district or write something like:
接下来你必须UPDATE为每个区写这样的's 或者写一些类似的东西：
WITH vals(district_id numeric, province_id numeric) as
( VALUES((1234,13), -- pairs of district_id , province_id
(1235,13),
(1236,13),
........) -- write the pair for every district
)
UPDATE district as dist
SET province_id = vals.province_id
FROM vals
WHERE dist.district_id = vals.district_id
