Обсуждение: Insert data in two columns same table

> "drum.lucas@gmail.com" <drum.lucas@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?



--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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);

assuming those two queries 1 and 2 return multiple rows, which rows of junk.wm_260_billables2  match up with what rows of public.ja_mobiusers  ?

your schema is very poorly defined.  I think you need to take a class in relational database design and usage, or read a good book on it at least..

 
the *CORRECT* SOLUTION WOULD BE MORE LIKE

INSERT INTO dm.billables_links (billable_id, mobiuser_id) SELECT b.billable_id, m.id from billables b inner join ja_mobiusers m on b.billable_id = ... where ......

I left ... in because your code fragments are referencing fields that aren't even IN your tables, and your tables don't have sane references.

-- 
john r pierce, recycling bits in santa cruz

> >
> 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.

lets start from here. you have 2 tables:

test=*# select * from source1;
 i
---
 1
 2
 3
(3 rows)

test=*# select * from source2;
 i
---
 1
 2
 3
(3 rows)


You can combine this 2 tables via cross join:

test=*# select * from source1 cross join (select * from source2) x;
 i | i
---+---
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 2
 2 | 3
 3 | 1
 3 | 2
 3 | 3
(9 rows)


as you can see there are 9 different combinations. You can insert all the
different combinations into a destination table:


test=*# create table destination (s1 int, s2 int);
CREATE TABLE
test=*# insert into destination select * from source1 cross join (select * from
source2) x;
INSERT 0 9
test=*# select * from destination ;
 s1 | s2
----+----
  1 |  1
  1 |  2
  1 |  3
  2 |  1
  2 |  2
  2 |  3
  3 |  1
  3 |  2
  3 |  3
(9 rows)


That's all, or? Keep in mind: you have N * M different combinations from the 2
tables.









--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

>
>
> assuming those two queries 1 and 2 return multiple rows, which rows of
> junk.wm_260_billables2  match up with what rows of public.ja_mobiusers  ?
>
> your schema is very poorly defined.  I think you need to take a class in
> relational database design and usage, or read a good book on it at least..
>
>
> the *CORRECT* SOLUTION WOULD BE MORE LIKE


yepp, full ack.


--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 03/16/2016 07:07 PM, drum.lucas@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),
>                  );
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com