Insert in 2 tables with last_insert_id ()

Question

Insert in 2 tables with last_insert_id ()

Navigation

#1 by (0 votes)

0

Good day, I'm trying to make an insert in 2 tables that are related by the main ID of one that is the FK of the other. I'm using a Stored Procedure for this, but I get an error when I try to load data that is: Column count does not match value count at row 1 .

Here I add my Stored Procedure .

CREATE DEFINER='root'@'localhost' PROCEDURE 'Informacion'(
                 //Tabla1

        in _dato1 varchar(100),
        in _dato2 varchar(100), 
        in _dato3 varchar(100), 
        in _fecha date,
                //Tabla 2

        in _dato4 varchar(100),
        in _dato5 varchar(100),
        in _dato6 varchar(100),
        in _tabla1_FK int(11)

)
BEGIN
Insert into tabla1 values(_dato1, _dato2, _dato3, curdate());
Insert into tabla2 values(_dato4, _dato5, _dato6, last_insert_id());
SELECT MAX(idtabla1) from tabla1;
END

Let's say that in the parameter _table1_FK should go the value of the id of table1.

I do not know if the error is found in the parameters declared at the beginning (where I do not mention the id of both tables since they are auto-incremental) or in the use of last_insert_id () .

mysql stored-procedure

asked by Eliza 05.12.2017 в 17:00

source

1 answer

Validate with unique more than one field in Lavaravel 5.1? Problem to work with Django

score 0 · Accepted Answer

The error is in the syntax of INSERT that you use:

Insert into tabla1 values(_dato1, _dato2, _dato3, curdate());
Insert into tabla2 values(_dato4, _dato5, _dato6, last_insert_id());

In both cases, you are not explicitly specifying the columns to which you are going to assign value. In this case, MySQL expects you to provide values for all the columns in the table. But from your description, I understand that you are actually omitting the self-reinforcing column.

The solution is to explicitly name the columns to which you are assigning value in INSERT . Something like this:

Insert into tabla1 (dato1, dato2, dato3, fecha) values(_dato1, _dato2, _dato3, curdate());
Insert into tabla2 (dato4, dato5, dato6, fk_id) values(_dato4, _dato5, _dato6, last_insert_id());

Sure, I do not know the names of the columns in your tables. So replace:

(dato1, dato2, dato3, fecha)
(dato4, dato5, dato6, fk_id)

... with the real names on your tables.