Re: Update two tables returning id from insert CTE Query

[Patrick B <patrickbakerbr@xxxxxxxxx>]

2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@xxxxxxxxx>:

Hi guys,

I've got 2k rows in a table:

CREATE TABLE
    public.not_monthly
    (
    id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
    clientid BIGINT,
    name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
    name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

I want to insert data from public.not_monthly to public.table_1(clientid,name_first) and public.table_2(client_id,c_id(FROM table_1),name_last)

table_2.c_id must have the ID from the insert on the table_1 table.

I did this:

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
)

Then, I was able to select the table_1.id using:

SELECT i.id AS table_1_id, s.id AS not_monthly_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn) 

So I'd imagine now I would do the update? How can I update table_2.c_id with the ins_table_1.id value?

I'm using Postgres 9.2

Thanks

Patrick

I'm doing this now:

sel AS (
SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id = 
(
  SELECT c_id
  FROM sel
  ORDER BY c_id
) 
WHERE clientid = 124312;

But I get ERROR:  more than one row returned by a subquery used as an _expression_