My SQL-fu isn't up to much, but my reaction was along the lines of SELECT *, LAST_UPDATED=GET_PROCEDURE() FROM TABLE WHERE LAST_UPDATE > LAST_UPDATED;
An optimiser may be able to optimise this for you, depending how complicated GET_PROCEDURE() is. If it's just a select from yet another table, the optimiser should spot it's constant and it'll fly. You might be able to declare it constant, I don't know. (And don't trust my example syntax! :-) Cheers, Wol On 19/07/12 17:29, Brian Leach wrote: > George > > It depends on the db type. In SQL Server, for example, you would create a > stored procedure that would capture the subquery result to a local variable > and use that. > > e.g. > > DECLARE @lastUpdate DATETIME; > SELECT @lastUpdate = some_expression ; > select * from table where last_update > @lastUpdate > > But I don't know with mySQL - it's a bit short on intellect - but I see it > has a SELECT .. INTO format for populating a variable. Might be worth a > shot. > > > Brian > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of George Gallen > Sent: 19 July 2012 16:56 > To: U2 Users > Subject: [U2] MySQL query qestion.... > > This is specific to MySQL, but could apply to any SQL DB. > > Is there a way to put the results of one query to be used by another, > without having that second query run on each Iteration of the sub query? > > Example: > > SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE()); > > The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself > with a new timestamp. > > My workaround is: > > SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE()); SELECT > UPDATE_PROCEDURE(); > > Here the GET_PROCEDURE() only retrieves a value, and doesn't update it. > > BUT...if there were half a million records in the TABLE, I'd prefer not to > run the GET procedure half a million times > To retrieve the same result which could add significant processing time > (this would be run for each of the tables > I'm mirroring on UV from MySQL . > > George _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
