Getting the value of the previous query

Question

I have a procedure that includes a number of query

DELIMITER $$
CREATE PROCEDURE test()

BEGIN

    SELECT * FROM a;
    INSERT INTO b ...;          //use result a query
    UPDATE c ...;               //use result a query
    INSERT INTO d ...;          //use result a query
    SELECT * FROM d WHERE ...;  //use result a query
    INSERT INTO e ...;          //use result d query
    UPDATE a ...;               //use result d query

END 
$$

How can I get value from previous queries?

And

How can I detect if it was null?

score 3 · Answer 1 · edited May 05 '23 at 08:03

3

If you are inserting one row at a time and you want the the most recent auto-incremented id, you can get the id using;

select last_insert_id()

This is documented here.

If they are inserting multiple rows, then this can be a little bit trickier.

If you want to "return" a value from a select, use variables . . . either := or into.

edited May 05 '23 at 08:03

Glorfindel

21,988
13
81
109

answered Nov 04 '17 at 02:29

Gordon Linoff

1,242,037
58
646
786

My first query is select. Then, by taking last_insert_id(), another user may be taken. And I do not just want an id – Omid Naji Nov 04 '17 at 02:34
Can you please give me a small example? – Omid Naji Nov 04 '17 at 02:40

score 0 · Answer 2 · answered Nov 04 '17 at 02:54

I would suggest you use "temp table". It is temporary tables that you can create to store your data and perform query on it as per that session.

So, it could be something like this..

DELIMITER $$ CREATE PROCEDURE test()

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS tempTable_A AS (SELECT * FROM a)

INSERT INTO b ...;          //use result tempTable_A  query
UPDATE c ...;               //use result tempTable_A  query
INSERT INTO d ...;          //use result tempTable_A  query

CREATE TEMPORARY TABLE IF NOT EXISTS tempTable_D AS (SELECT * FROM d) //use result tempTable_A  query
INSERT INTO e ...;          //use result tempTable_D  query
UPDATE a ...;               //use result tempTable_D  query



END  $$

Example could be found here: Create a temporary table in a SELECT statement without a separate CREATE TABLE

Documentation link: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

Getting the value of the previous query

2 Answers2