Update Query

[pradchal1]

I have the following table

ID No Color

A 2 RED
A 1 RED
B 3 RED
B 4 RED
C 2 RED
D 2 RED
D 1 RED

And my desired output Table

A 2 RED
A 1 BLUE
B 3 BLUE
B 4 RED
C 2 RED
D 2 RED
D 1 BLUE

Basically the Color column has to be changed to BLUE for for those ID's that are present more than once while 'No' less than the other one.

Any help.

 

[gmmastros]

Here's some code to get you started. The trick is joining the table to itself.

-- Setup the Test Data
	Declare @Table Table(Id VarChar(10), No Integer, Color VarChar(10))

	Insert Into @Table(Id, No, Color) Values('A',   2   ,'RED')
	Insert Into @Table(Id, No, Color) Values('A',   1   ,'RED')
	Insert Into @Table(Id, No, Color) Values('B',   3   ,'RED')
	Insert Into @Table(Id, No, Color) Values('B',   4   ,'RED')
	Insert Into @Table(Id, No, Color) Values('C',   2   ,'RED')
	Insert Into @Table(Id, No, Color) Values('D',   2   ,'RED')
	Insert Into @Table(Id, No, Color) Values('D',   1   ,'RED')

-- Display the Before Data
	Select * from @Table

-- Here's the real code
	Update 	A
	Set 	A.Color = 'BLUE'
	From	@Table A
			Inner Join @Table B On A.ID = B.ID And A.No < B.No

-- Display the after data
	Select * from @Table

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

That's very interesting, George. I played around with it a little bit. In this case, the And clause in the join could also be a standard where clause, couldn't it? I'm curious to understand better when it would make a difference. Can you explain a bit?

Thanks,

Bob

 

[gmmastros]

Bob,

The query could also have been written like this...

	Update 	A
	Set 	A.Color = 'BLUE'
	From	@Table A,
			@Table B 
	Where 	A.ID = B.ID And A.No < B.No

or

	Update 	A
	Set 	A.Color = 'BLUE'
	From	@Table A
			Inner Join @Table B On A.Id = B.Id
	Where 	A.No < B.No

In my opinion, the original way (with the AND condition in the From clause) is preferable because it defines how the tables are joined.

For me... if the codnition specifies how the tables are joined, I put the condition in the from clause. If the condition is referrring to a filter, I will put it in the where clause.

I've seen vongrunt do this sort of join at least a dozen times since I've joined the tek-tips community. I would be curious to hear his thoughts on this. I've learned a lot from him and respect his opinion (and many others) very much.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pradchal1]

Thanks George.

 

[BobRodes]

Very interesting, George. Thanks for elaborating.

Bob