Replace certain values in Field1 with values from Field2 in same table

Transact-SQL
1

Hello Everyone. I would like to replace certain values from a field called [ReportVersion] with the values from another field called [Mode]. Not all of the values in the [ReportVersion] field need to be replaced. Both fields are in the same table. Can someone please help with this?

Example: the [ReportVersion] field has the following values: Call, SMS/TEXT, Instant Messaging/Email. I would like to replace all SMS/TEXT and Instant Messaging/Email values, with the value that is in the Mode field. The mode field will tell me if the value should be SMS, TEXT, EMAIL, or IM.

Thank you!

2

update MyTable set ReportVersion = Mode

1 Like
3 
UPDATE YourTable
SET [ReportVersion] = [Mode]
WHERE [ReportVersion] IN ('SMS/TEXT','Messaging/Email')
1 Like
4

note that that will replace ReportVersion with Mode for EVERY row in the table, which is not what the O/P seems to want.

At the very least when doing this I put

BEGIN TRANSACTION
... my UPDATE code ...
-- COMMIT
-- ROLLBACK

so the UPDATE is in a transaction. If it goes wrong I can highlight "ROLLBACK" and execute that; if it looks OK I can do a few SELECTs to check how the data looks, and only when I am happy do I then highlight "COMMIT" and execute that. Note that the table will be locked from when you start the transaction to when you either Commit or Rollback, and that will block other users who try to access that table.

(I am assuming that the update will change many/most of the rows in the table, so on that basis SQL is likely to escalate any Row Locks to a Table Lock, but if not then only parts of the table will be blocked)

2 Likes
5

Hi James. Thank you for your response. From your solution it seems that I would have to do this to a table. Can I use the UPDATE function for a view as well?

6

You can update a view, but there are some restrictions. Look for the section "Updatable Views" in this page.