Sql sintax从2个表中插入数据 [英] Sql sintax to insert data from 2 tables

问题描述

您好，



由于我对MySql语法不太好，我在这里要求的语法可以插入值并更新2个表。< br $>




情景：



我有一张名为Entradas的表格一个叫做赛义德。两者都有数据和hora列。考虑到这一点，因为entradas的数据和hora列意味着汽车在该日期和时间加入，并且来自所述的列数据和hora的值意味着是当我点击一个按钮插入当前日期和时间时插入。我正在请求一个sql语法，可以将值data和hora插入表saidas并更新entradas的值sa 这等于0，我希望按钮按下时更改为1 ...任何消化？



我尝试过：



################################# #############################

解决方案

你不能使用单个sql语句更新两个表。



您可以做的是将两个语句合并到一个事务中 - 参见 MySQL事务 [ ^ ]

这样两种语句都将被执行，或者它们都不会执行（换句话说，如果没有完成相应的更新，您将无法在一个表上获得更新在另一张桌子上）



您可以将这些更新放入存储过程中 - 参见 MySQL存储过程教程 [ ^ ]这意味着您将从代码中进行单个SQL调用 - 请参阅 MySQL :: MySQL Connector / Net开发人员指南:: 6.10.1使用来自Connector / Net的存储例程 [ ^ ]



这是一个部分工作的例子（信用卡 [ ^ ]）

 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 ;
- 将查询插入'data'和'hora'到[saidas]这里
- < span class =code-comment>将您的查询更新为[entradas]此处的'sai'
IF ' _ rollback' 那么
SET retcode = 0 ;
ROLLBACK ;
ELSE
SET retcode = 1 ;
COMMIT ;
END IF ;
END

DELIMITER;

说明：

代码将创建一个存储过程称为'sp_entradas_sai'，它接受一个输入参数'ident' - 但是这将是你要识别哪一行entradas需要更新 - 车辆ID或类似。将此参数的类型更改为您的id在entradas表上的任何类型。

该过程将返回单个值'retcode'和整数，其中0表示失败，1表示成功。



该过程声明一个处理程序告诉MySQL如果出现错误该怎么办 - 在这种情况下，将局部变量'_rollback'设置为1.如果发生错误MySQL将设置此值，然后继续执行该过程中的下一行。



我们然后启动事务...我已经离开了查询以实际执行您需要做的更新。

当他们完成任务后，更改将被提交到数据库（COMMIT）或已发生的任何部分更改将被撤消（ROLLBACK）。这是该事务的结束，在这种情况下，程序结束。



请注意，return参数的值是根据我们是COMMIT还是ROLLBACK。



然后你可以用这样的代码调用这个程序

 MySqlCommand cmd = < span class =code-keyword> new  MySqlCommand（）; 
 cmd.CommandText =   add_emp; 
 cmd.CommandType = CommandType.StoredProcedure; 
 
 cmd.Parameters.AddWithValue（  @ ident，  car1）;  //  或任何您的ID  
 cmd.Parameters [  @ ident]。Direction = ParameterDirection.Input; 
 
 cmd.Parameters.AddWithValue（  @ retcode，MySqlDbType。< span class =code-sdkkeyword> Int32 ）; 
 cmd.Parameters [  @ retcode]。Direction = ParameterDirection.Output; 
 
 cmd.ExecuteNonQuery（）; 
  var  res = cmd.Parameters [  @retcode]值; 

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:

##############################################################

解决方案

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[^])

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

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;

这篇关于Sql sintax从2个表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！