Is there a quick way to update many records in SQL?
I need to replace over 20,000 names with new names generated with CodeID.
For example: I have to update all lines containing "dog" (with CodeID 1) with "cat" and update all lines containing "horse" (with Code 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 instructions work, but I need a faster way to do it because I have over 20,000 names.
Thanks in advance.
This is the fastest way to do it.
Or do you want to update all records in one command?
you can do the update with a connection (Fixed Syntax ... Havent used this for 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 shorten the table lock time ... But the total update time will take longer (this is just a performance improvement with a high degree of accuracy). You can do this by updating only certain ranges of identifiers in each batch.
You can also have this data in a separate table. Since the data is not normalized. Take it out so it becomes more normalized.
You might want to create a temporary table that contains 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 this:
update MYTABLE
set myvalue = t.to
where myvalue = t.from
from MYTABLE m,
#TRANSLATIONS t
(Untested, from the top of the head).
You can use CASE statement to update it
UPDATE for animals SET SET Description: = CASE codeID WHEN 1 THEN "cat" WHEN 2 THEN "BIRD" .... END
Suppose you have a file like this:
1,cat 2,bird ...
I would write a script that reads this file and does an update on every line.
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);
What you can do to speed it up is only to 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 forces the command to update only those entries that are not already "cat".
Disclaimer: I hate cats.