MyChenQL, part III
 

How do I combine these queries into one?
[sql]
UPDATE forum SET something = "value1" WHERE forumid=first
UPDATE forum SET something = "value2" WHERE forumid=second
UPDATE forum SET something = "value3" WHERE forumid=third
.
.
.
[/sql]

hmm, not sure if it's possible, but if then i'd say with this query:

[sql]UPDATE forum SET something = IF(forumid=first,"value1",IF(forumid=second,"value 2", "value3")) WHERE forumid IN (first, second, third)[/sql]

but i really not sure if it works...
i just can say it doesn't look good ;)

So the only two options are to make one incredibly complex query or to *dramatic music* query in a loop?

yes i think so....

Which one would be preferable, then?

depends on what you want.

faster should be the if-way (if it really works ;))
but nicer is the reapeated while.

if it wouldn't produce too much queries on an often called page, i'd use the while-method.
if it's an page where it's important of how much queries are on, i'd use the if method, but code it better readable in a half while or recursive method to get the querystring :)

Here you go what you wanted:

UPDATE forum AS f1, forum AS f2, forum AS f3 SET f1.something = "value1", f2.something = "value2", f2.something = "value3" WHERE f1.forumid=first AND f2.forumid=second AND f3.forumid=third;

Now these queries won't be incredibly server-intensive, will be?

I find it hard to believe that MySQL doesn't have this built in :(

just use

EXPLAIN whatever your query is
so
EXPLAIN SELECT * FROM user WHERE username='person';

Codename&PPN: Everyday you can learn something new :)
thx :)

Cool:)

Satan

Not working:

I just remembered: you can't update multiple tables at once. So at this rate I'd have to use 36 queries (!) or some other method.

ahh good to know :)

have you tested my suggestion?
perhaps this works..

Investigating...

Ah. I knew this type of query worked wonders on shortening selects but didn't actually test it on an update. Sorry to hear it doesn't work.

[n00b mode]
XENON YOU'RE A GENIUS!
[/n00b mode]
It worked. The query took relatively long to execute (~1 sec) but this will be a cron'ed script anyway :)

Thanks :D

IIRC multiple tables are supported in MySQL 4 but since practically nobody uses it (including myself) I want to steer clear of it :)

good to hear it works :)
you're welcome :)

have you compared the running time with the added time of 36 single queries?
i think it's faster :) but don't know exactly..

It probably is faster but just the concept of using that many is pretty bad IMO.

think you are right :)
if it's croned it hasn't to be that clear to understand it just must work :)