MyChenQL, part III

[Chris M]

Cool

Satan

[filburt1]

Originally posted by Codename49
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;

Not working:

Database error in vBulletin 2.2.9:

Invalid SQL: UPDATE forum AS f0, forum AS f1, forum AS f2, forum AS f3, forum AS f4, forum AS f5, forum AS f6, forum AS f7, forum AS f8, forum AS f9, forum AS f10, forum AS f11, forum AS f12, forum AS f13, forum AS f14, forum AS f15, forum AS f16, forum AS f17, forum AS f18, forum AS f19, forum AS f20, forum AS f21, forum AS f22, forum AS f23, forum AS f24, forum AS f25, forum AS f26, forum AS f27, forum AS f28, forum AS f29, forum AS f30, forum AS f31, forum AS f32, forum AS f33, forum AS f34, forum AS f35 SET f0.lastthreadtitle = 'Layout and Quicktime issu...', f1.lastthreadtitle = 'Use A String Value From W...', f2.lastthreadtitle = 'Save as target..more info', f3.lastthreadtitle = 'Feedback', f4.lastthreadtitle = 'help me.', f5.lastthreadtitle = 'Am doing some research on...', f6.lastthreadtitle = 'Cool web-editor...', f7.lastthreadtitle = 'Member of the Month (Octo...', f8.lastthreadtitle = 'Need new Vid Card...', f9.lastthreadtitle = 'Have fun ', f10.lastthreadtitle = '...and now Chit Chat is o...', f11.lastthreadtitle = 'I M IN A BAD MOOD! I WANT...', f12.lastthreadtitle = 'MSN! GRR!', f13.lastthreadtitle = 'Notating Time in a MySQL ...', f14.lastthreadtitle = 'CPU - Computer Power User', f15.lastthreadtitle = 'Scepter 17" moniter', f16.lastthreadtitle = 'Direct Connect Addendum.....', f17.lastthreadtitle = 'Visual Basic: Adding Func...', f18.lastthreadtitle = 'Case Modding Tutorial', f19.lastthreadtitle = 'Hosting Matters', f20.lastthreadtitle = 'Kedaman...your music?', f21.lastthreadtitle = 'Best way to store passwor...', f22.lastthreadtitle = 'DEADNESS!', f23.lastthreadtitle = 'Eminem - The Eminem Story', f24.lastthreadtitle = 'What software do you use?', f25.lastthreadtitle = 'new', f26.lastthreadtitle = 'Navigation bar \ No frame...', f27.lastthreadtitle = 'Wednesday, August 28, 200...', f28.lastthreadtitle = 'WEB DESIGNER/PROMOTER REQ...', f29.lastthreadtitle = 'Make Objects Circular...i...', f30.lastthreadtitle = 'Andy Z!', f31.lastthreadtitle = 'Member of the Month', f32.lastthreadtitle = 'Pages within Pages, step ...', f33.lastthreadtitle = 'Archive of Ripped Sites', f34.lastthreadtitle = 'Hosting Matters', f35.lastthreadtitle = 'Tucows Domain Name Regist...' WHERE f0.forumid = 3 AND f1.forumid = 6 AND f2.forumid = 8 AND f3.forumid = 11 AND f4.forumid = 12 AND f5.forumid = 13 AND f6.forumid = 16 AND f7.forumid = 18 AND f8.forumid = 28 AND f9.forumid = 34 AND f10.forumid = 38 AND f11.forumid = 39 AND f12.forumid = 49 AND f13.forumid = 53 AND f14.forumid = 54 AND f15.forumid = 57 AND f16.forumid = 58 AND f17.forumid = 59 AND f18.forumid = 60 AND f19.forumid = 62 AND f20.forumid = 64 AND f21.forumid = 67 AND f22.forumid = 77 AND f23.forumid = 78 AND f24.forumid = 84 AND f25.forumid = 85 AND f26.forumid = 86 AND f27.forumid = 87 AND f28.forumid = 88 AND f29.forumid = 89 AND f30.forumid = 93 AND f31.forumid = 94 AND f32.forumid = 95 AND f33.forumid = 96 AND f34.forumid = 97 AND f35.forumid = 98
mysql error: You have an error in your SQL syntax near 'AS f0, forum AS f1, forum AS f2, forum AS f3, forum AS f4, forum AS f5, forum AS' at line 1

mysql error number: 1064

Date: Monday 16th of December 2002 01:28:15 PM
Script: http://localhost:8082/web/testvb/rep...adtitlecache=1
Referer:

[filburt1]

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.

[Xenon]

ahh good to know

have you tested my suggestion?
perhaps this works..

[filburt1]

Investigating...

[okrogius]

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.

[filburt1]

[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

[filburt1]

Originally posted by Codename49
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.

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

[Xenon]

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..

[filburt1]

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