Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Need help with SQL command, please..

Quote Reply
Need help with SQL command, please..
Hi all,

I am trying to update the category for some links currently listed in a different ones
What should be the proper syntax for this:

UPDATE lsql_CatLinks SET CategoryID=ID WHERE lsql_Links Title=SOME_WORD_HERE....

problem is both are different tables...how do I merge them...


any help appretiated

Koki
Quote Reply
Re: [Koki] Need help with SQL command, please.. In reply to
Hi,

If your db doesn't support subselects, then you need to do:

SELECT ID FROM lsql_Links WHERE Title="..."

into a file, then:

UPDATE lsql_CatLinks SET CategoryID=123 WHERE LinkID IN (list of id's from file);

If you are on a database that supports subselects, you could do it with:

UPDATE lsql_CatLinks SET CategoryID=123 WHERE LinkID IN ( SELECT ID FROM lsql_Links WHERE Title="..." );

Edit to add: Any changes like this will require a repair table to reset category counts.

Cheers,

Alex
--
Gossamer Threads Inc.

Last edited by:

Alex: Apr 12, 2005, 6:06 PM
Quote Reply
Re: [Alex] Need help with SQL command, please.. In reply to
Thank you Alex,
It is MySQL 4.0.22 so I suppose everything is supported.
I'll give it a try now.

Greets
Koki
Quote Reply
Re: [Koki] Need help with SQL command, please.. In reply to
4.0.22 (upgrade to 4.0.24 -- some security issues) does not support sub selects. It's a 4.1.x feature unless i missed something.

http://dev.mysql.com/tech-resources/crash-me.php?res_id=47

Thanks
HyTC
==================================
Mail Me If Contacting Privately Is That Necessary.
==================================
Quote Reply
Re: [HyperTherm] Need help with SQL command, please.. In reply to
Hey HyperTerm, thanks you told me this . I upgraded MySQL to 4.1.10a-standard and I am still tracking for problems with my scripts using SQL, but one I can tell you, man is that version fast. Everything react with triple speed as far as I can tell.


P.S. Still did not try Alex's command...

Koki
Quote Reply
Re: [Koki] Need help with SQL command, please.. In reply to
I am sure you must have referred to the upgrade docs (4.0.x --> 4.1.x) and surely must have done a backup should you need to fall back to 4.0.x. Really by upgrade i meant within the same tree (4.0) which currently stands at 4.0.24 or soemthing.

http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

Thanks
HyTC
==================================
Mail Me If Contacting Privately Is That Necessary.
==================================
Quote Reply
Re: [HyperTherm] Need help with SQL command, please.. In reply to
Yes I did, and I preserved the password style usage too in case it has the ugly idea of making problems with the php appl. I have on my server. So far, no problems at all and everything running much faster. I'm kinda happy:-)Sly

Greets
Koki
Quote Reply
Re: [Alex] Need help with SQL command, please.. In reply to
Hi Alex,

I tried your command , but the problem is that if I do not specify the EXACT name of the title it won't list the ID's. What command should be to look for IF TITLE CONSIST="one or two word's of the title"

P.S.
I am on MySQL 4.1.10a standart

Thanks
Koki
Quote Reply
Re: [Koki] Need help with SQL command, please.. In reply to
Hi,

WHERE Title LIKE '%word1%'

The % is a wildcard and matches anything. I'd be very careful though, and just select ID,Title first to see you are actually getting the rows you want.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Need help with SQL command, please.. In reply to
Works perfect, thank you very much:-)

Greets,
Koki