On 22-Jun-01 Jean-Philippe Cote wrote:
>
>
> Hello,
>
> Suppose I want to update n rows (where n is a possibly large number)
> using the mySQL C API. I can create n separate UPDATE
> statements and send each of them to the server with mysql_[real_]query(),
> but that doesn't seem particularly efficient to me. Is there a better
> way to do it ? Can I create a single string containing all UPDATE
> statements (comma/semi-colon separated ???) and send this string
> only once ?
If the action is the same for the records you can do:
UPDATE foo set bar='blah' WHERE id in (1,55,96,437 ...)
(See yesterday's thread:
mailto: <[EMAIL PROTECTED]>
On size limitations & work-arounds. )
If action is different for each of the id's then you're stuck with
one statement per record.
For really huge (n) you might want to consider a tmp table:
CREATE TABLE tmp_tbl ( ...);
INSERT INTO tmp_tbl (field names)
SELECT (unchanged), (changed) FROM original, updates
WHERE original.id=updates.id;
REPLACE INTO original
SELECT * from tmp_tbl;
DROP TABLE tmp_tbl,updates;
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
