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

SQL Server 2000 Forums

Transact-SQL (2000)

Update Column

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-15 : 14:30:16

Someone fat fingered our customer's part number, and this make 301 incorrect entries in our database.

I can view the incorrect records with this query:

SELECT Test_Result
FROM dbo.ACP_Parts
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

I can view a corrected version of the records with this query:

SELECT Test_Result, Replace(Test_Result, 'R6791J', 'R6951J') AS 'Test_Result2'
FROM dbo.ACP_Parts
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

Here's what I'm thinking about running to make the change to these records:

UPDATE dbo.ACP_Parts
SET Test_Result=Replace(Test_Result, 'R6791J', 'R6951J')
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

Could someone please verify for me that this will make the changes that I want to the Test_Result string field?

Avoid Sears Home Improvement

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 14:34:20

looks good. You could run your update in a transaction and commit or rollback in cases like these when you are not sure.

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-15 : 14:47:10

Thanks. I don't do updates very often.

I wasn't sure if the update statement needed the rows further defined somehow.

How do I write in commit/rollback functionality?

Avoid Sears Home Improvement

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 16:53:29

[code]Begin tran

UPDATE dbo.ACP_Parts
SET Test_Result=Replace(Test_Result, 'R6791J', 'R6951J')
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

--carry your checks, sip coffee etc

Commit --if it looks good
rollback-- if it doesn't.[/code]

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 10:32:39

quote:
Originally posted by jp2code

Someone fat fingered our customer's part number, and this make 301 incorrect entries in our database.

I can view the incorrect records with this query:
SELECT Test_Result
FROM dbo.ACP_Parts
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

I can view a corrected version of the records with this query:
SELECT Test_Result, Replace(Test_Result, 'R6791J', 'R6951J') AS 'Test_Result2'
FROM dbo.ACP_Parts
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

Here's what I'm thinking about running to make the change to these records:
UPDATE dbo.ACP_Parts
SET Test_Result=Replace(Test_Result, 'R6791J', 'R6951J')
WHERE (WorkOrder_Number='465174') AND (System_ID Like '%Label%')

Could someone please verify for me that this will make the changes that I want to the Test_Result string field?

Avoid Sears Home Improvement

See What your does...
it replaces in the Column test_result this query finds the string part R6791J and replace this string part with this string part R6951J.......
so i dont think that here is any problem in your query if u seems the correct results then ur update query also do the correct work,,,,
So It seems well but if u still have doubt then ,,use sakets_2000 suggestion as he suggested in previous reply.
Thanks,,,

jp2code
Posting Yak Master

175 Posts

Posted - 2009-01-16 : 11:20:35

Thanks to both!

I was able to make the UPDATE without any issues, and that was what I wanted.

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources