> "drum.lucas@xxxxxxxxx" <drum.lucas@xxxxxxxxx> 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 omittedDavid J
