I was about to post a question, and then I was embarrassed to find
that I already asked the question more than 2 years ago, and I am
still unsure of the answer. The question was:
What is the fastest way to change the values in one column in every
row? What I thought of was like so:
BEGIN TRANSACTION;
UPDATE MyTable SET TheCol=7 WHERE keyCol=1;
UPDATE MyTable SET TheCol=8 WHERE keyCol=2;
... and so on for each row
COMMIT;
"Greg Obleshchuk" <[EMAIL PROTECTED]> wrote on 10 Jan 2004:
That's it, now make sure you have an index on KeyCol1 and your there.
I had neglected to mention that keyCol is an INTEGER PRIMARY KEY
column. I have since read that it is counterproductive to make an
index on an INTEGER PRIMARY KEY column.
Kurt Welgehausen <[EMAIL PROTECTED]> wrote on 10 Jan 2004:
What you propose will work, but you haven't given enough
info for anyone to tell you whether it's the best solution.
I'm not sure what other information would be helpful. The table has
38 columns, a few of which may be fairly large blobs. The number of
rows is obviously variable, but let's say it's on the order of 1000.
The only other approach I've thought of goes like this:
* Create a temporary table TChange with two columns TValue and TKey,
holding the updated values of TheCol and corresponding values of
keyCol.
* Create another temporary table TNew with the same columns as MyTable.
* INSERT INTO TNew
SELECT <columns of MyTable, except with TValue instead of TheCol>
FROM MyTable, TChange
WHERE MyTable.keyCol = TChange.TKey;
* DROP TABLE MyTable;
* CREATE TABLE MyTable <same columns as before>;
* INSERT INTO MyTable SELECT * FROM TNew;
* DROP TABLE TNew;
* DROP TABLE TChange;
I could try it, of course. I'm just curious whether someone with a
better mental model of what the database engine is doing would easily
see whether this makes sense.
--
James W. Walker, ScriptPerfection Enterprises, Inc.
<http://www.write-brain.com/>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
