如何使用其他表中的不同值更新多行?

我有3个表,其中两个包含日期,最后一个表是尝试从这些表中提取数据 . 但是,其列的两个(如果我们不计算id列)包含数据,只有这个表有 .

所以,我正在尝试做的是通过多次更新从一个列中导入数据,一次一列 .

表格具有以下结构:

test1(
  id_test1 serial NOT NULL,
  typeactemariage character varying(200) NOT NULL,
  datemariage character varying(200),
  id_lieumariage integer,
  id_conjoint integer,
  id_beaupere integer,
  id_bellemere integer,
  CONSTRAINT test1_pkey PRIMARY KEY (id_test1)
)

test2
(
  id_test2 serial NOT NULL,
  ville character varying(200),
  departement character varying(200) NOT NULL,
  CONSTRAINT test2_pkey PRIMARY KEY (id_test2)
)

test3
(
  id_test3 serial NOT NULL,
  typeacte character varying(100) NOT NULL,
  epoux character varying(100) NOT NULL,
  prenomepoux character varying(100),
  prenompereepoux character varying(100),
  nommereepoux character varying(100),
  prenommereepoux character varying(100),
  epouse character varying(100),
  prenomepouse character varying(100),
  prenompereepouse character varying(100),
  nommereepouse character varying(100),
  prenommereepouse character varying(100),
  lieu character varying(150) NOT NULL,
  dept character varying(100) NOT NULL,
  dates character varying(30),
  numvue character varying(100),
  CONSTRAINT test3_pkey PRIMARY KEY (id_test3)
)

test1已经在typeactemariage和datemariage(和id_test1)列中包含数据,并尝试将来自test2(id_test2)的数据放入列(id_lieumariage)中 . 其他列目前仍为空 .

这就是我写的:

UPDATE
    test1 
SET
    id_lieumariage = l.id_test2
FROM
    test1 m,
    test2 l,
    test3 o
WHERE
    o.id_test3 = m.id_test1
    AND o.lieu = l.ville
    AND o.dept = l.departement;

代码执行,列有数据,但它只提取第一个值(在第1行),这不是我想要的 . 有没有办法让它做一个循环,在每一行上做这个更新?

谢谢 !


编辑:我正在放置我的 table 的照片,并试图更好地解释 .

test1:http://puu.sh/scVCy/8479b7e4f2.png

test2:http://puu.sh/scVmL/901df2d74a.png

test3:http://puu.sh/scVoz/04be151c71.png

执行我的代码后的test1:http://puu.sh/scVpC/3209b5f4a6.png

id_lieumariage列使用它获得的第一个值 1 填充自身 . 使用我的代码,我希望第一行的值为1,第二行的值为2,第三行的值为3 .

我有一些像我必须导入的100k值(我在这里只显示3行,试图简化我的例子) .

所以,我需要的是能够为每一行获得不同的 id_lieumariage 值 . 告诉我,如果你没有非常好的英语 .


编辑2:我要提供我的数据:

CREATE TABLE test1()
ALTER TABLE test1 ADD COLUMN id_test1 SERIAL PRIMARY KEY, ADD COLUMN typeactemariage VARCHAR(200) NOT NULL, ADD COLUMN datemariage VARCHAR(200), ADD COLUMN id_lieumariage INTEGER, ADD COLUMN id_conjoint INTEGER, ADD COLUMN id_beaupere INTEGER, ADD COLUMN id_bellemere INTEGER;

CREATE TABLE test2()
ALTER TABLE test2 ADD COLUMN id_test2 SERIAL PRIMARY KEY, ADD COLUMN ville VARCHAR(200), ADD COLUMN departement VARCHAR(200) NOT NULL*;

CREATE TABLE test3()
ALTER TABLE test3 ADD COLUMN id_test3 SERIAL PRIMARY KEY, ADD COLUMN typeacte VARCHAR(200) NOT NULL, ADD COLUMN epoux VARCHAR(200), ADD COLUMN prenomepoux VARCHAR(200), ADD COLUMN prenompereepoux VARCHAR(200), ADD COLUMN nommereepoux VARCHAR(200), ADD COLUMN prenommereepoux VARCHAR(200), ADD COLUMN epouse VARCHAR(200), ADD COLUMN prenomepouse VARCHAR(200), ADD COLUMN nommereepouse VARCHAR(200), ADD COLUMN prenommereepouse VARCHAR(200), ADD COLUMN lieu VARCHAR(200), ADD COLUMN dept VARCHAR(200), ADD COLUMN dates VARCHAR(200), ADD COLUMN numvue VARCHAR(200);

INSERT INTO test1(typeactemariage, datemariage, id_lieumariage, id_conjoint, id_beaupere, id_bellemere)
VALUES ('Contrat de mariage', '21/11/2016', NULL, NULL, NULL, NULL),
('Contrat de mariage', '25/11/2016', NULL, NULL, NULL, NULL)
('Contrat de mariage', '11/11/2016', NULL, NULL, NULL, NULL);

INSERT INTO test2(ville, departement)
VALUES('Royan', '17'),
('Rochefort', '17'),
('Aytre', '17');

INSERT INTO test3(typeacte, epoux, prenomepoux, prenompereepoux, nommereepoux, prenommereepoux, epouse, prenomepouse, prenompereepouse, nommereepouse, prenommereepouse, lieu, dept, dates, numvue)
VALUES ('Contrat de mariage', 'DUPOND', 'Loris', 'Jacques', 'Duponne', 'Jeanne', 'BOURDOIN', 'Manon', 'Dujonni', 'Jinny', 'Royan', '17', '11/11/2016', '10/70'),
('Contrat de mariage', 'DUPONT', 'Pierre', 'Jerome', 'Dupuits', 'Jeannette', 'BOURLIAN', 'Philippine', 'Marpa', 'Elisa', 'Rochefort', '17', '21/11/2016', '10/20'),
('Contrat de mariage', 'Tintin', 'Milou', 'Haddock', 'Bond', 'Barack', 'Dutroux', 'Anna', 'Trane', 'Melissa', 'Aytre', '17', '25/11/2016', '10/25');

What I'm supposed supposed to have after the execution of my request is the following :

test1:1的行(1),Contrat de mariage,21/11 / 2016,1,NULL,NULL,NULL

test1:1的第(2)行,Contrat de mariage,25/11 / 2016,2,NULL,NULL,NULL

test1:1的第(3)行,Contrat de mariage,11/11 / 2016,3,NULL,NULL,NULL

What I do get :

test1:1的行(1),Contrat de mariage,21/11 / 2016,1,NULL,NULL,NULL

test1:1的第(2)行,Contrat de mariage,25/11 / 2016,1,NULL,NULL,NULL

test1:1的行(3),Contrat de mariage,11/11 / 2016,1,NULL,NULL,NULL

我的代码正在做的是获取它找到的第一个l.id_test2,并将它放在每一行上 . 如果我修改请求有:

UPDATE
    test1 
SET
    id_lieumariage = l.id_test2
FROM
    test1 m,
    test2 l,
    test3 o
WHERE
    o.id_test3 = m.id_test1
    AND o.lieu = l.ville
    AND o.dept = l.departement
    AND id_test1 = 2

然后,我得到的是:

test1:1的行(1),Contrat de mariage,21/11 / 2016,1,NULL,NULL,NULL

test1:1的第(2)行,Contrat de mariage,25/11 / 2016,1,NULL,NULL,NULL

test1:1的行(3),Contrat de mariage,11/11 / 2016,1,NULL,NULL,NULL

如果您需要更多信息,请告诉我!

回答(2)

2 years ago

update test1 m
set id_lieumariage = l.id_test2
from
    test3 o
    inner join
    test2 l on o.lieu = l.ville and o.dept = l.departement
where o.id_test3 = m.id_test1
;

table test1;
 id_test1 |  typeactemariage   | datemariage | id_lieumariage | id_conjoint | id_beaupere | id_bellemere 
----------+--------------------+-------------+----------------+-------------+-------------+--------------
        1 | Contrat de mariage | 21/11/2016  |              1 |             |             |             
        2 | Contrat de mariage | 25/11/2016  |              2 |             |             |             
        3 | Contrat de mariage | 11/11/2016  |              3 |             |             |

2 years ago

我在编辑之前的答案是针对MySql,因为MySql标签愚弄了我一点点,我在MySql数据库上测试了以前的查询 . 我目前没有选择为postgresql测试这些查询,但也许他们会帮助你 .

UPDATE
  test1
SET
  id_lieumariage = subquery.id_test2
FROM (
  SELECT id_test2, id_test1
  FROM test1 m, test2 l, test3 o
  WHERE o.id_test3 = m.id_test1
    AND o.lieu = l.ville
    AND o.dept = l.departement) AS subquery
WHERE test1.id_test1 = subquery.id_test1;

在您执行更新之前,您可以使用此附加查询来测试更新将影响的行数:

SELECT
  count(*)
FROM (
  SELECT id_test2, id_test1
  FROM test1 m, test2 l, test3 o
  WHERE o.id_test3 = m.id_test1
    AND o.lieu = l.ville
    AND o.dept = l.departement) AS subquery
WHERE test1.id_test1 = subquery.id_test1;

select 子查询应提供已连接的3个表,其中包含所有要求,外部 update 查询应查找'subquery'表,以便在test1表中设置适当的值 .