CODEWITHSUNDEEP
mysqlsqlsql-insert
Jason K
Jason K

Reputation: 65

Two unique inserts from one select

I'm using a InnoDB/memcache as a temp holder for rows not processed and placed into it's table with indexes (which slowed down the initial load by a lot and we need to handle spikes). The table is as follows

memcached/innodb: c1|c2|c3|c4

I need to get that data now into two tables such as u1|c1|c2|c3 (u1 is a new unique id generated by auto_inc of that table) u1|c4 (u1 is copied from previous table so it's same)

Upvotes: 1

Views: 36

Answers (1)

Mureinik
Mureinik

Reputation: 311308

I'd first insert into the first table, and then query it to insert into the second one:

INSERT INTO t1 (c1, c2, c3) -- u1 is left out, so it can be generated
SELECT c1, c2, c3
FROM   INPUT

INSERT INTO t2 (u1, c4)
SELECT t1.u1, input.c4
FORM   t1
JOIN   t1.c1 = input.c1 AND
       t1.c2 = input.c2 AND
       t1.c3 = input.c3

Upvotes: 1

Related Questions