Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

WWWXXXAPC, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text, WWWXXXAPC., XXXCNZZZ

Links

XODOX
Impressum

#1: Stored Procedure Question

Posted on 2011-09-21 20:23:31 by Brandon Phelps

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:

@row_id = SELECT id FROM myTable WHERE <blah blah> LIMIT 1;

IF @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@m.gmane.org

Report this message

#2: Re: Stored Procedure Question

Posted on 2011-09-21 20:34:50 by Derek Downey

SELECT id INTO @row_id FROM myTable WHERE <blah blah> LIMIT 1;

Source=20
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,
>=20
> I would like to create a stored procedure that does the following:
>=20
> 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
>=20
> 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?
>=20
> Something like this:
>=20
> @row_id =3D SELECT id FROM myTable WHERE <blah blah> LIMIT 1;
>=20
> IF @row_id !=3D nothing THEN
> UPDATE myTable ...
> ELSE
> INSERT INTO anotherTable ...
> END IF
>=20
> So if no rows were returned from the select I perform the ELSE block, =
otherwise I perform the main IF block.
>=20
> Thanks!
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dderek@orange-pants.com
>=20


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

Report this message

#3: Re: Stored Procedure Question [SOLVED]

Posted on 2011-09-21 20:41:37 by Brandon Phelps

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 @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:
>>
>> @row_id = SELECT id FROM myTable WHERE<blah blah> LIMIT 1;
>>
>> IF @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@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@m.gmane.org

Report this message