2

I want to upgrade all columns in a table, what I am aiming to do is retreive a column from the row it will update then update it, something like:

update works 
set encrpyted_item_no = (CAST(RTrim((
    select unencrypted_item_no 
    from works 
    where name = name
) AS VARBINARY(50))

I know that query is wrong, it's just an example to show you what I am aiming to do.

I want it to select the column unencrypted_item_no from its row then update that same row with the data it gets from unencrypted_item_no, doing this for the whole table.

How would I accomplish this?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
PicksAndPony
  • 299
  • 1
  • 2
  • 8
  • So if you have two rows with the same name, and one of them has item_no = 5 and the other has item_no = 3, which one wins? – Aaron Bertrand Jul 09 '12 at 15:21
  • If it's from the same exact row, then you should not use a subquery, as Bort and Gaby suggested. If it's data from the same table but not necessarily that same row then you'd do an additional FROM. – Pablo Romeo Jul 09 '12 at 15:28

2 Answers2

5

You shouldn't need to do a sub-select, referencing the other column in the set will work on a row by row basis, ie:

UPDATE works 
SET encrpyted_item_no = CAST(RTrim(unencrypted_item_no) AS varbinary(50))
Bort
  • 7,398
  • 3
  • 33
  • 48
2

Shouldn't this be enough ?

update works 
set encrypted_item_no = CAST(RTrim(unencrypted_item_no) AS VARBINARY(50))
Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317