On 18 March 2016 at 03:23, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 03/16/2016 07:07 PM, drum.lu...@gmail.com 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),
>> );
>>
>>
>>
>
>
>
I just did it using:
> INSERT INTO dm.billables_links (billable_id, mobiuser_id)
> SELECT billable_id
> , (SELECT id
> FROM public.ja_mobiusers
> WHERE name_first LIKE 'Anthon%'
> AND name_last LIKE 'Swile%') AS foo -- alias irrelevant
> FROM dm.billables
> WHERE info ILIKE '%Anthon%' AND info ILIKE '%Swile%' AND account_id =
> 32152 ;
