update column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update column

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2013-08-23 : 08:37:11

Hello,

I have two tables:

dtr

dataid........Subtype

data

docid....mimetype

I need to change the value of column mimetype in table data, but only for these entries which have the value 144 in column "Subtype" in table dtr.

here is my sql:



with cte as (
select 
DataID, 
SubType, 
MimeType
 

 from dtr A1, data A2 

where A1.dataid=A2.Docid And A1.Subtype='144'

AND 

A2.mimetype='application/news-message-id'



)



update cte set MimeType = 'application/x-outlook-msg'

I've tested it and it works. Do you see any problems with this SQL?

As updates are risky I'll do a database backup. Is it possible to get all changed entries so that I can track my update, because I don't have a report about the changes? Maybe that everythin is transferred to an output file?

Kind regards,

Lara

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 08:43:45

For your requirement the following UPDATE is enough...
UPDATE DataTable
SET mimetype = 'application/x-outlook-msg'
WHERE docid IN (SELECT dataid FROM dtrTable WHERE Subtype = '144')
AND mimetype = 'application/news-message-id'

NOTE:Your UPDATE operation will also works, but looks like complex way....

--
Chandu

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2013-08-25 : 14:56:44

Hi,

thanks for your help. Just one question. As an update can be risky sometimes, I'd like to see the result of my update somewhere...best would be an output file...is there an option to track an update query?

Greetings....

Lara

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-25 : 18:27:16

Yes. Use the OUTPUT option.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-27 : 01:46:21

quote:
Originally posted by musclebreast

Hi,

thanks for your help. Just one question. As an update can be risky sometimes, I'd like to see the result of my update somewhere...best would be an output file...is there an option to track an update query?

Greetings....

Lara

what we do is to wrap the update inside explicit transaction and see output after update then depending on result whether its expected one or not we do COMMIT/ROLLBACK

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources