更新和拆分逗号分隔的列数据到其他列

Question

表如下

id, column, col1, col2 
1, abc-def, 

基本上我需要拆分列1和更新col1和COL2

select split_part(column, '-', 1) as col1, split_part(column, '-', 2) as col2 from table 

我可怎么办更新和选择同时使用id。

Answer 1

# CREATE TEMPORARY TABLE tmp_split (id SERIAL PRIMARY KEY, c0 VARCHAR(32), c1 VARCHAR(16), c2 VARCHAR(16)); 
CREATE TABLE 

# INSERT INTO tmp_split (c0) VALUES ('one-two'), ('three-four'); 
INSERT 0 2 

# SELECT * FROM tmp_split; 
id |  c0  | c1 | c2 
----+------------+----+---- 
    1 | one-two | | 
    2 | three-four | | 
(2 rows) 

# UPDATE tmp_split SET c1 = split_part(c0, '-', 1), c2 = split_part(c0, '-', 2); 
UPDATE 2 

# SELECT * FROM tmp_split; 
id |  c0  | c1 | c2 
----+------------+-------+------ 
    1 | one-two | one | two 
    2 | three-four | three | four 
(2 rows) 

Answer 2

update the_table 
    set col1 = split_part(column, '-', 1), 
     col2 = split_part(column, '-', 2)