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?
Hi Andreas!
Well...
There are two tables that I need to get data from(dm.billables / public.ja_mobiusers), and a third table (dm.billables_links) that I need to insert data from those two tables.
The table dm.billables has four (important) columns:
billable_id / customer_id / role_id / mobiuser_id
I wanna add data there. The data is not there yet, so it's not an UPDATE.
1 - select the billable_id: (SELECT1)
SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'
2 - select the mobiuser_id: (SELECT2)
SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dadryl%'
3 - Insert those two data into the dm.billables_links table (EXAMPLE):
INSERT INTO dm.billables_links (billable_id, mobiuser_id) VALUES (SELECT1, SELECT2);
CREATE TABLE
billables
(
billable_id BIGINT DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass") NOT
NULL,
account_id BIGINT NOT NULL,
code CHARACTER VARYING(64) NOT NULL,
info "TEXT",
CONSTRAINT pk_billables PRIMARY KEY (billable_id),
CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
);
CREATE TABLE
billables_links
(
billable_link_id BIGINT DEFAULT "nextval"('"dm"."billables_links_billable_link_id_seq"'::
"regclass") NOT NULL,
billable_id BIGINT NOT NULL,
customer_id BIGINT,
role_id BIGINT,
mobiuser_id BIGINT,
CONSTRAINT pk_billables_links PRIMARY KEY (billable_link_id),
CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id) REFERENCES billables
(billable_id),
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)
);
CREATE TABLE
ja_mobiusers
(
id BIGINT DEFAULT "nextval"('"ja_mobiusers_id_seq"'::"regclass") NOT NULL,
clientid BIGINT DEFAULT 0,
[...]
PRIMARY KEY (id),
CONSTRAINT fk_account_must_exist FOREIGN KEY (clientid) REFERENCES ja_clients (id),
);
