Click here to Skip to main content
15,890,897 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

Since i'm not that good with MySql Syntaxes, i'm here asking for a syntax that could insert values and update 2 tables.


Scenario:

I have a table called "Entradas" and one called "Saidas". Both have the column "data" and "hora". With that in mind, since the "data" and "hora column of "entradas" mean that a car joined at that date and time, and the values from the columns "data" and "hora" of "saidas" are mean to be inserted as i click a button, inserting the current date and time. I'm requesting a sql syntax that could insert the values "data" and "hora" into the table "saidas" and update a value of "entradas" called "sai" which is equal to 0 and i want it to change to 1 on button press... Any sugestion?

What I have tried:

##############################################################
Posted
Updated 4-Mar-16 3:17am

1 solution

You can't update two tables with a single sql statement.

What you could do is combine the two statements into a transaction - see MySQL Transaction[^]
That way either both statements will be executed or neither of them will (in other words you won't get an update on one table without the corresponding update being done on the other table)

You could put these updates into a stored procedure - see MySQL Stored Procedure Tutorial[^] which means you would have a single sql call from your code - see MySQL :: MySQL Connector/Net Developer Guide :: 6.10.1 Using Stored Routines from Connector/Net[^]

[EDIT] Here is a partially worked example (credit[^])
SQL
DELIMITER $$

CREATE PROCEDURE 'sp_entradas_sai'(
IN ident VARCHAR(20), OUT retcode INT)
BEGIN
    DECLARE '_rollback' BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET '_rollback' = 1;
    START TRANSACTION;
    -- Put your query to insert 'data' and 'hora' to [saidas] here
    -- Put your query to update 'sai' on [entradas] here
    IF '_rollback' THEN
        SET retcode = 0;
        ROLLBACK;
    ELSE
        SET retcode = 1;
        COMMIT;
    END IF;
END$$

DELIMITER ;

Explanation:
The code will create a stored procedure called 'sp_entradas_sai' which takes one input parameter 'ident' - this will be however you are going to identify which row of entradas needs to be updated - the vehicle id or similar. Change the type of this parameter to whatever type your id is on the entradas table.
The procedure will return a single value 'retcode' and integer where 0 means "failed" and 1 means "succeeded".

The procedure declares a handler telling MySQL what to do if there is an error - in this case, set a local variable '_rollback' to 1. If an error occurs MySQL will set this value and then continue to the next line in the procedure.

We then start the transaction ... I've left the queries to do the actual updates for you to do.
When they have done either the changes will be committed to the database (COMMIT) or any partial changes that have happened will be undone (ROLLBACK). That is the end of that transaction and in this case, the end of the procedure.

Note that the value of the return parameter is set according to whether we COMMIT or ROLLBACK.

You can then call this procedure from your code something like this
C#
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "add_emp";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ident", "car1");	// or whatever your id is
cmd.Parameters["@ident"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("@retcode", MySqlDbType.Int32);
cmd.Parameters["@retcode"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
var res = cmd.Parameters["@retcode"].Value;
 
Share this answer
 
v2
Comments
Scribling Doodle 4-Mar-16 9:19am    
You didn't understood, i just want to update one value on table "entradas" which is "sai" to value 1 on button click, and to insert the "data" and "hora" on the table "saidas"
CHill60 4-Mar-16 9:23am    
My solution still applies - you are trying to update two separate tables - although one of those "updates" is actually an insert.
Scribling Doodle 4-Mar-16 9:25am    
well, I'm not much into it xD Could you give me a "light" on this one? ;)
CHill60 4-Mar-16 11:07am    
I've added a partially worked example to my solution with some explanations. More detail is on the links I provided and in the MySQL documentation.
NB - I was not able to test any of the code so there may be some errors, and you will probably have to change some of the variable types to match your database
Scribling Doodle 7-Mar-16 3:55am    
Yeah it worked as good as it looks! Thanks once more ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900