Bulk update huge table

I have a table that has 26 columns with a couple of thousand records. Is there a way to bulk update this table?

Right now I just do a standard update on each column that needs updati开发者_JAVA技巧ng. E.g.

update mytbl
set col1 = replace(....)

update mytbl
set col1 = col1 + "xyz"

update mytbl
set col2 = "test..."

I know that for each record the statements above will update the column for all records first before going to next column(s).

I'm looking for a "quick bulk update" as this is taking too long.

Thanks

---

You can use this structure:

UPDATE myTable
SET col1 = REPLACE(col1.....),
    col2 = 'some value'

---

1 - A couple thousand records x 26 fields isn't huge :)

2 - Either use the multiple-field update syntax ck suggests, or insert it into a new table which may be faster:

SELECT (REPLACE(col1...)) as Col1,
       'Some Value' as Col2,
       ...
INTO MyNewTable
FROM MyOldTable

---

These advices are more useful for much bigger number of records, but might help anyway: 1. Use multifield update as suggested by ck

1. If it is possible lock the table for exclusive access during the update.

2. Disable all of the indexes. Restore them after update finished.

---

Please take care of following points.

1. You can use SQL Profiler to to check that whether any trigger is doing something during your updation process.

2. You can disable the indexes. Run your test now and finally restore it.