On 03/16/2016 07:07 PM, drum.lucas@xxxxxxxxx wrote:
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)
Would it not be easier if instead of customer_id, role_id, mobiuser_id
you had id_type('customer', 'role', 'mobi') and user_id(id). Then you
could eliminate the CHECK, which as far as I can see is just restricting
entry to one user id anyway.
);
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),
);
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
