SQL Query help - 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

SQL Query help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-17 : 08:59:14

Hi All

Iam trying to replace the column values in a table based on two other columns in the same table

Sample Data

ClaimNo LineNo Flag Procedurecode
100 01 N MN4567
100 02 Y 7863
100 03 N MN8976
100 04 Y 9000
101 01 Y 8954
101 02 N MN6754
101 03 N MN7654
101 04 Y 8976
102 01 Y 1234
102 02 Y 2345
102 03 Y 3456
102 03 Y 4567

Every ClaimNo column has multiple rows of data.But if procedurecode column for a claimNo starts with MN then all the associated values with the claimno for the flag column should change to N

So the data should become like below

ClaimNo LineNo Flag Procedurecode
100 01 N MN4567
100 02 N 7863
100 03 N MN8976
100 04 N 9000
101 01 N 8954
101 02 N MN6754
101 03 N MN7654
101 04 N 8976
102 01 Y 1234
102 02 Y 2345
102 03 Y 3456
102 03 Y 4567

lazycoder
Starting Member

12 Posts

Posted - 2010-12-17 : 10:24:56

You can use this:


update thetable set
	Flag = 'N'
from (SELECT ClaimNo FROM thetable WHERE LEFT(Procedurecode, 2) = 'MN') as tmp
where thetable.ClaimNo = tmp.ClaimNo
	AND thetable.Flag = 'Y'

But you should consider restructuring your tables if you need to do this regularly.

-----------------
http://it.expertmonster.com/