SQL CUT and PASTE to COLUMN

1.8k views Asked by At

I know how to copy data from one column to another:

UPDATE table SET columnB = columnA

This is cool, but I want to batch this command (I know how to do this) because I'll need to update a few (160) million records. The problem is that if I select the TOP(1000) (per batch) there is no way of knowing I processed the first 1000 (where with delete, the TOP(1000) will keep changing, since you delete them so there is a new top every time). I can't handle this with a simple WHERE clause, since the values in columnB are ranging from true values to 'NULL' to '0', and the same goes for column A.

So basicly, I'd like to go through each record, check columnA somewhat like:

WHERE columnA <> NULL

Then SET columnB with this columnA data and then somehow set columnA to NULL, so the next batch will skip this record (since columnA <> NULL is false).

Summary question: how to set columnA to NULL after updating columnB with the data from columnA?

1

There are 1 answers

1
Guy Levin On BEST ANSWER

You can use :

UPDATE   TableName
SET      Col2 = Col1,
         Col1 = NULL
WHERE    Col1 IS NOT NULL