Gossamer Forum
Home : Products : Gossamer Links : Discussions :

How to use GT:SQL module to do this query/delete....

Quote Reply
How to use GT:SQL module to do this query/delete....
I'm trying to do this:

DELETE FROM table WHERE uid='5' AND id NOT IN (SELECT * FROM (SELECT id FROM table WHERE uid='5' ORDER BY id DESC LIMIT 20) AS TAB)

and in case you were wondering... The above query will look at table, and the where the UID = 5, it will delete all but the last 20 records.

I know I could easily do this with perl, and a few queries... However letting mysql do it all in one seems so much more.... right...

Any ideas on this would be extremely helpful!

Thanks!

AlexJ
Quote Reply
Re: [AlexJ] How to use GT:SQL module to do this query/delete.... In reply to
Unfortunately, GT::SQL doesn't support doing sub-selects at the moment. You would have to do that as two separate queries with GT::SQL (or of course you could do a raw query).

Adrian
Quote Reply
Re: [brewt] How to use GT:SQL module to do this query/delete.... In reply to
Adrian,

Thanks for the info...Bummer!

I've always tried to say within the supported functionality, so I'm not familiar with doing a raw query with the GT package... Can you point me in the right direction?

Thanks,

AlexJ
Quote Reply
Re: [AlexJ] How to use GT:SQL module to do this query/delete.... In reply to
Pretty simple to do a query with GT::SQL;

Code:
my $sth = $DB->table('Whatever')->do_query(qq|SELECT * FROM Whereever|) || die $GT::SQL::error;

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] How to use GT:SQL module to do this query/delete.... In reply to
Thanks Andy....

That did the trick!