Menu
I am a complete newbie to MySql so please be gentle.
Is there an equivalent of the RETURNING clause in Oracle or the Inserted'/'Deleted tables in SQL Server in MySQL? What I want to do is the following:
Insert the deleted set of rows into table B.
Please help!
Thanks
857
The RETURN statement in MySQL is used to end the stored functions. Each stored function should have at least one RETURN statement. This is used only in functions in stored procedures triggers or, events you can use LEAVE instead of RETURN.
The RETURN statement is used to unconditionally and immediately end an SQL procedure by returning the flow of control to the caller of the stored procedure. When the RETURN statement runs, it must return an integer value. If the return value is not provided, the default is 0.
To return a value from stored procedure, you need to use user defined session specific variable. Add @ symbol before variable name. Now second call for difference of two values. Call the stored procedure.
MySQL stored functions only return a single scalar value. They cannot return result sets. Functions can be used in a scalar expression context. You can use a stored procedure to return a result set, but you can't use it in an expression.
Unfortunately, you can't do both insertion and deletion in one query, but you can do it all in one transaction if you are using a transactional store engine (like InnoDB). Moreover, RETURNING is supported by Oracle and PostgreSQL but not by MySQL and therefore you need to write separate delete and insert statements.
Using a transaction however, will guarantee that only the successfully copied data will be deleted from tableA. Consider the following:
begin transaction;
insert into tableB select * from tableA where 'your_condition_here';
delete from tableA where 'your_condition_here';
commit;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
