10

I want to make a insert into 2 tables

visits:

visit_id int | card_id int

registration:

registration_id int | type enum('in','out') | timestamp int | visit_id  int

I want something like:

INSERT INTO `visits` as v ,`registration` as v
(v.`visit_id`,v.`card_id`,r.`registration_id`, r.`type`, r.`timestamp`, r.`visit_id`) 
VALUES (NULL, 12131141,NULL, UNIX_TIMESTAMP(), v.`visit_id`);

I wonder if its possible

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Spidfire
  • 5,433
  • 6
  • 28
  • 36

4 Answers4

11

It's not possible with one query as INSERT can only insert data to one table in mysql. You can either

  1. write this as two queries and execute them as a batch
  2. create a stored procedure that would execute two insert command

You can wrap those inserts in transaction if you need to make sure that both queries will write the data.

RaYell
  • 69,610
  • 20
  • 126
  • 152
8

It seems like the problem you are trying to solve is to get the auto-increment value from the "visits" row to insert into "registration". Am I right?

If so, you can just use the LAST_INSERT_ID() function like this:

INSERT INTO `visits` (`visit_id`,`card_id`) 
VALUES (NULL, 12131141);
INSERT INTO `registration` (`registration_id`, `type`, `timestamp`, `visit_id`) 
VALUES (NULL, 'in', UNIX_TIMESTAMP(), LAST_INSERT_ID());
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
0

You can always do something like this

INSERT IGNORE INTO `table2` VALUES ((select id from table1 where col="value"), 3, 4, 5)
supersan
  • 5,671
  • 3
  • 45
  • 64
-1
INSERT INTO designation as de,
                department as da,
                    profile as pr

                                  (designation_name,
                                   depart_id,
                                   id,
                                   username,
                                   department,
                                   designation)


      select de.designation_name,
             de.depart_id,da.id,
             pr.username,
             pr.department,
             pr.designation 
       from 
              designation,
              department,
              profile

          de.designation_name='project manager' AND de.id='1'         OR
          de.depart_id='2' AND de.id='2'                              OR  
          da.id='2'                                                   OR  
          pr.username='kapil.purohit' AND pr.id='9' AND pr.status='1' OR 
          pr.department='1' AND pr.id='9'                             OR  
          pr.designation='3' AND pr.id='9' AND pr.status='1'

       WHERE               
               de.id    =   da.id   AND
                   da.id    =   pr.id   AND
                   de.id    =   pr.id   AND
                                    ORDER BY de.id DESC
Barmar
  • 741,623
  • 53
  • 500
  • 612
kapil
  • 7
  • 1
  • 1
    Is this MySQL syntax? In MySQL 5.5 I'm getting a syntax error. – rustyx Apr 11 '14 at 13:56
  • 1
    Syntax error caused by WHERE in wrong place and an unnecessary AND before the ORDER BY. This code relates to the question, but doesn't really answer it specifically. It also appears to be invalid logically and syntactically. – Johnathan Elmore Dec 19 '14 at 14:33