Oi pessoal,
WITH rows AS (
SELECT
t1.id,
t1.clientid,
t1.name_first,
t1.name_last
row_number() OVER (ORDER BY t1.id) AS rn
FROM
public.not_monthly t1
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
SELECT
clientid,
name_first
FROM rows
RETURNING id
),
ins_table_2 AS (
INSERT INTO public.table_2 (name_last,clientid)
SELECT
name_last,
clientid
FROM rows
RETURNING id
)
r AS (
select c_id, ROW_NUMBER() OVER () AS rn
FROM ins_table_1
),
t AS (
select id, ROW_NUMBER() OVER () AS rn
FROM rows
)
-- SELECT r.c_id FROM r JOIN t USING (rn)
UPDATE table_2 SET c_id =
(
SELECT r.c_id FROM r JOIN t USING (rn)
)
WHERE clientid = 124312
RETURNING *;
Com a query a cima eu recebo o erro: ERROR: more than one row returned by a
subquery used as an expression
Mas, o select retorna o c_Id como esperado: SELECT r.c_id FROM r JOIN t
USING (rn)
O que estou fazendo de errado?
public.not_monthly = Tabela que contém dados para importar (importado de um
CSV)
public.table_1 = Tabela para importar clientid e name_first
public.table_2 = Tabela para importar clientid, name_last e c_id (ID da
table_1 - Este id deve respeitar a ordem..)
clientid é igual para todas as rows, ou seja, não posso me basear nela
(WHERE clientid = .... )
Obrigado!
Patrick
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
