Select & Update in one query?

#1

I'm doing something like this:

SELECT id, title, text FROM table ORDER BY rand() LIMIT 3;

Then I run some PHP, extract IDs from array, and build an update query
like this:

UPDATE table SET selected=selected+1 WHERE id=1234 OR id=4321 OR
id=5678;

My question is, is there a way to do both the SELECT and UPDATE from a
single query, instead of using all of the PHP to get the IDs and then
running a second query?

TIA,

Jason

#2

jwcarlton wrote:

Quote:

I'm doing something like this:

SELECT id, title, text FROM table ORDER BY rand() LIMIT 3;

Then I run some PHP, extract IDs from array, and build an update query
like this:

UPDATE table SET selected=selected+1 WHERE id=1234 OR id=4321 OR
id=5678;

My question is, is there a way to do both the SELECT and UPDATE from a
single query, instead of using all of the PHP to get the IDs and then
running a second query?

yes. :-)

UPDATE table SET selected=selected+1 WHERE id in (SELECT id, title, text
FROM table ORDER BY rand() LIMIT 3);

At a wild improbable guess..;-)

Quote:

TIA,

Jason

#3

Quote:

I'm doing something like this:

SELECT id, title, text FROM table ORDER BY rand() LIMIT 3;

Then I run some PHP, extract IDs from array, and build an update query
like this:

UPDATE table SET selected=selected+1 WHERE id=1234 OR id=4321 OR
id=5678;

My question is, is there a way to do both the SELECT and UPDATE from a
single query, instead of using all of the PHP to get the IDs and then
running a second query?

yes. :-)

UPDATE table SET selected=selected+1 WHERE id in (SELECT id, title, text
FROM table ORDER BY rand() LIMIT 3);

At a wild improbable guess..;-)

Would I then be able to access the SELECTed fields in PHP?

#4

On Sep 26, 5:37*pm, jwcarlton <jwcarl... (AT) gmail (DOT) com> wrote:

Quote:

I'm doing something like this:

SELECT id, title, text FROM table ORDER BY rand() LIMIT 3;

Then I run some PHP, extract IDs from array, and build an update query
like this:

UPDATE table SET selected=selected+1 WHERE id=1234 OR id=4321 OR
id=5678;

My question is, is there a way to do both the SELECT and UPDATE from a
single query, instead of using all of the PHP to get the IDs and then
running a second query?

yes. :-)

UPDATE table SET selected=selected+1 WHERE id in (SELECT id, title, text
FROM table ORDER BY rand() LIMIT 3);

At a wild improbable guess..;-)

Would I then be able to access the SELECTed fields in PHP?

No.

#5

onedbguru wrote:

Quote:

On Sep 26, 5:37 pm, jwcarlton <jwcarl... (AT) gmail (DOT) com> wrote:
I'm doing something like this:
SELECT id, title, text FROM table ORDER BY rand() LIMIT 3;
Then I run some PHP, extract IDs from array, and build an update query
like this:
UPDATE table SET selected=selected+1 WHERE id=1234 OR id=4321 OR
id=5678;
My question is, is there a way to do both the SELECT and UPDATE from a
single query, instead of using all of the PHP to get the IDs and then
running a second query?
yes. :-)
UPDATE table SET selected=selected+1 WHERE id in (SELECT id, title, text
FROM table ORDER BY rand() LIMIT 3);
At a wild improbable guess..;-)
Would I then be able to access the SELECTed fields in PHP?

No.

UPDATE returns no row data.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Select & Update in one query?

comp.databases.mysql comp.databases.mysql

Select & Update in one query? - 09-23-2011 , 11:09 PM

Re: Select & Update in one query? - 09-24-2011 , 03:23 AM

Re: Select & Update in one query? - 09-26-2011 , 04:37 PM

Re: Select & Update in one query? - 09-27-2011 , 08:36 PM

Re: Select & Update in one query? - 09-28-2011 , 03:06 AM