Hello all,

I would like to create a stored procedure that does the following:

1. Accepts 4 values as parameters
2. SELECTS 1 record (LIMIT 1) from a table where the 4 parameters match fields in that table
a. If a record was returned then UPDATE the table
b. If a record was not returned then INSERT into a different table

My main question here is how can I execute a SELECT id FROM ... LIMIT 1 statement within a stored procedure then use the returned id field later in the procedure?

Something like this:

[at] row_id = SELECT id FROM myTable WHERE <blah blah> LIMIT 1;

IF [at] row_id != nothing THEN
UPDATE myTable ...
ELSE
INSERT INTO anotherTable ...
END IF

So if no rows were returned from the select I perform the ELSE block, otherwise I perform the main IF block.

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2 [at] m.gmane.org

SELECT id INTO [at] row_id FROM myTable WHERE <blah blah> LIMIT 1;

Source
http://dev.mysql.com/doc/refman/5.5/en/select-into-statement .html

On Sep 21, 2011, at 2:23 PM, Brandon Phelps wrote:

> Hello all,
>
> I would like to create a stored procedure that does the following:
>
> 1. Accepts 4 values as parameters
> 2. SELECTS 1 record (LIMIT 1) from a table where the 4 parameters =
match fields in that table
> a. If a record was returned then UPDATE the table
> b. If a record was not returned then INSERT into a different =
table
>
> My main question here is how can I execute a SELECT id FROM ... LIMIT =
1 statement within a stored procedure then use the returned id field =
later in the procedure?
>
> Something like this:
>
> [at] row_id =3D SELECT id FROM myTable WHERE <blah blah> LIMIT 1;
>
> IF [at] row_id !=3D nothing THEN
> UPDATE myTable ...
> ELSE
> INSERT INTO anotherTable ...
> END IF
>
> So if no rows were returned from the select I perform the ELSE block, =
otherwise I perform the main IF block.
>
> Thanks!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dderek [at] orange-pants.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

Ah ha! Thanks Derek. I thought INTO was used strictly for inserting the selected records into another table.

Much appreciated.


On 09/21/2011 02:34 PM, Derek Downey wrote:
> SELECT id INTO [at] row_id FROM myTable WHERE<blah blah> LIMIT 1;
>
> Source
> http://dev.mysql.com/doc/refman/5.5/en/select-into-statement .html
>
> On Sep 21, 2011, at 2:23 PM, Brandon Phelps wrote:
>
>> Hello all,
>>
>> I would like to create a stored procedure that does the following:
>>
>> 1. Accepts 4 values as parameters
>> 2. SELECTS 1 record (LIMIT 1) from a table where the 4 parameters match fields in that table
>> a. If a record was returned then UPDATE the table
>> b. If a record was not returned then INSERT into a different table
>>
>> My main question here is how can I execute a SELECT id FROM ... LIMIT 1 statement within a stored procedure then use the returned id field later in the procedure?
>>
>> Something like this:
>>
>> [at] row_id = SELECT id FROM myTable WHERE<blah blah> LIMIT 1;
>>
>> IF [at] row_id != nothing THEN
>> UPDATE myTable ...
>> ELSE
>> INSERT INTO anotherTable ...
>> END IF
>>
>> So if no rows were returned from the select I perform the ELSE block, otherwise I perform the main IF block.
>>
>> Thanks!
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=derek [at] orange-pants.com
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2 [at] m.gmane.org