Is there a fast way to update many records in SQL?

Question

I need to replace more than 20 000 names with new names i created given the CodeID.

For example: I must update all rows that contain "dog" (which has a CodeID of 1) with "cat", and update all rows that contain "horse" (which has a CodeID of 2) with "bird", etc.

1st SQL statement: UPDATE animalTable SET cDescription = "cat" WHERE CodeID = 1

2nd SQL statement: UPDATE animalTable SET cDescription = "bird" WHERE CodeID = 2

These statements work, but i need a faster way to do this because i have over 20 000 names.

Thank you in advance.

Answer 1

What you could do to speed it up is to only update those records that don't already have the value you want to assign.

UPDATE animalTable SET cDescription = "cat" WHERE CodeID = 1 AND cDescription != "cat"

This approach makes the command only update those records that are not already 'cat'.

Disclaimer: I hate cats.

Answer 2

Thats the fastest way you can do it.

Or do you want update all records in a single command?

you can do a update with a join (Fixed Syntax... Havent used this one in a while)

UPDATE animalTable 
INNER JOIN CodeTable ON animalTable.CodeID = CodeTable.ID 
SET animalTable.cDescription = CodeTable.Description_1;

Another option is to split the updates into smaller batches, this will reduce the time the table is locked... But the total time of the updates will take longer (Its just an improvement of precieved Performance) You can do that by updating only certain ID ranges in each batch.

Also you could have that data in a separate table. Since the data is not normalized. Move it away so its more normalized.

Answer 3

You could use a CASE statement to update it

UPDATE animaltable SET cDescription = CASE codeID WHEN 1 THEN 'cat' WHEN 2 THEN 'bird'.... END

Answer 4

Supposind you have a file like this:

1,cat
2,bird
...

I would write a script which reads that file and executes an update for each row.

In PHP:

$f = fopen("file.csv","r")
while($row = fgetcsv($f, 1024)) {
    $sql = "update animalTable set cDescription = '".$row[1]."' where CodeID = ".$row[0];
    mysql_query($sql);
}
fclose($f);

Answer 5

You might want to create a temporary table that holds the translation values and update based on that.

For example:

create table #TRANSLATIONS
(
    from   varchar(32),
    to     varchar(32)
)

Then, insert the translation values:

insert into #TRANSLATIONS (from,to) values ('cat','dog')

Finally, update based on that:

update MYTABLE
set    myvalue = t.to
where  myvalue = t.from
from   MYTABLE m,
       #TRANSLATIONS t

(Untested, off the top of my head).