CODEWITHSUNDEEP
mysqlinsert
Spidfire
Spidfire

Reputation: 5523

Mysql insert into 2 tables

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

Upvotes: 10

Views: 34477

Answers (4)

supersan
supersan

Reputation: 6141

You can always do something like this

INSERT IGNORE INTO `table2` VALUES ((select id from table1 where col="value"), 3, 4, 5)

Upvotes: 0

kapil
kapil

Reputation: 7

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

Upvotes: -1

Ike Walker
Ike Walker

Reputation: 65547

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

Upvotes: 8

RaYell
RaYell

Reputation: 70414

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.

Upvotes: 11

Related Questions