From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> |
Cc: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Insert data in two columns same table |
Date: | 2016-03-17 02:16:03 |
Message-ID: | CAKFQuwZh6RgV5XXN5tKhqpXuJB-_8xTqmYwBsyf7xyUreTEviQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Mar 16, 2016 at 6:49 PM, Andreas Kretschmer <andreas(at)a-kretschmer(dot)de
> wrote:
>
>
> > "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> hat am 17. März 2016 um
> 02:34
> > geschrieben:
> >
> >
> > I'm trying to insert data from TABLE A to TABLE B.
> >
> > 1 - Select billable_id from dm.billable
> > 2 - Select mobiuser_id from ja_mobiusers
> > 3 - Insert the billable_id and the mobiuser_id to the dm.billables_links
> > table.
> >
> >
> > *FYI -* It has to be in the same transaction because the mobiuser_id must
> > go to the selected billable_id on the first select.
> >
> > Well... Would be something like:
> >
> > > INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT
> billable_id
> > > FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%')),
> > > INSERT INTO dm.billables_links (mobiuser_id) VALUES ((SELECT id FROM
> > > public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE
> > > 'Dson%'))
> >
> >
> >
> > The problem is that I need to do that at the same time, because of a
> > constraint:
> >
> > ALTER TABLE dm.billables_links
> > ADD CONSTRAINT cc_one_and_only_one_target CHECK ((("customer_id" IS
> > NOT NULL)::integer + ("role_id" IS NOT NULL)::integer + ("mobiuser_id"
> > IS NOT NULL)::integer) = 1);
> >
> > I'm having trouble by creating that SQL... can anyone help please?
>
>
> I see a lot of other problems: you have 3 independet tables. Your 2 queries
> (selects) returns 2 independet results, you can't use that for insert into
> the
> 3rd table. And i think, you are looking for an update, not insert. So you
> have
> to define how your tables are linked together (join).
>
> Can you explain how these tables are linked together?
>
>
If we assume both queries will only ever return, at most, one row:
INSERT INTO billables_links (customer_id, mobiuser_id, role_id)
SELECT customer_id, mobiuser_id, null AS role_id
FROM (SELECT customer_id FROM customer WHERE [...]) cust
FULL JOIN (
SELECT
mobiuser
_id FROM
mobiuser
WHERE [...]
) mobi
ON (true)
--basically a CROSS JOIN but allows for one of the sides to be omitted
David J
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-03-17 02:19:03 | Re: Insert data in two columns same table |
Previous Message | drum.lucas@gmail.com | 2016-03-17 02:07:32 | Re: Insert data in two columns same table |