On Wed, Mar 16, 2016 at 6:49 PM, Andreas Kretschmer <andr...@a-kretschmer.de
> wrote:
>
>
> > "drum.lu...@gmail.com" <drum.lu...@gmail.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
