Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Discussions :

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

Jun 3, 2010, 12:09 PM
AlexJ
User (93 posts)
Jun 3, 2010, 12:09 PM
Post #1 of 5
Views: 681
Shortcut
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
Jun 3, 2010, 1:00 PM
brewt
Staff (4071 posts)
brewt's avatar
Jun 3, 2010, 1:00 PM
Post #2 of 5
Views: 670
Shortcut
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
Jun 3, 2010, 1:59 PM
AlexJ
User (93 posts)
Jun 3, 2010, 1:59 PM
Post #3 of 5
Views: 670
Shortcut
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
Jun 3, 2010, 3:18 PM
Andy
Veteran / Moderator (17366 posts)
Andy's avatar
Jun 3, 2010, 3:18 PM
Post #4 of 5
Views: 661
Shortcut
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

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates
Jun 4, 2010, 6:01 AM
AlexJ
User (93 posts)
Jun 4, 2010, 6:01 AM
Post #5 of 5
Views: 644
Shortcut
Quote Reply
Re: [Andy] How to use GT:SQL module to do this query/delete.... In reply to
Thanks Andy....

That did the trick!