update multiple columns of table depending on values of other columns

[king117]

I have table with following 5 fields

ID, Type, Code, Value, Pos.

I have to update Value and Pos fields depending on code and type fields.

1)If two rows have same code value and type values are ADD and DELETE then update Value with A and B and Pos with 1 and 2.
if i has another two rows with same scenario then do same thing as above but the Pos values must be 3 and 4.
and so on...

I hope i explained it well.How you do it in a stored procedure.

Thanks for your help.

 

[king117]

If i have following data in the data (in this table we will always have only two rows with same Code value)

ID Type Code Value Pos
1 ADD S1 NULL NULL
2 ADD S2 NULL NULL
3 DELETE S2 NULL NULL
4 DELETE S1 NULL NULL
5 ADD S3 NULL NULL
6 ADD S3 NULL NULL

In above table i have S1 and S2 codes have ADD and DELETE type values and S3 has both ADD values.So i want to update Value and Pos values as follows

ID Type Code Value Pos
1 ADD S1 A 1
2 ADD S2 A 3
3 DELETE S2 B 4
4 DELETE S1 B 2
5 ADD S3 X NULL
6 ADD S3 X NULL

In words if a code has both ADD and DELETE then i want to update the Value and Pos so that ADD row has Vlaue A and Pos 1 and DELETE row has Value B and Pos 2.If another code has both ADD and DELETE then Value is same but Pos values should be 3 and 4.if another Code has those two types then Values are same but Pos should be 5 and 6.we have to increment Pos values for evry code.
We don't update Pos column if we don't have both ADD and DELETE.

I hope i gave enough details..

Thanks for your help.

 

[gmmastros]

Here ya go.

I create a table Variable (@Temp) to store your original data. You should replace @Temp with your actual table name.

Declare @Temp 
Table   (Id Integer, 
        Type VarChar(20), 
        Code VarChar(10), 
        Value VarChar(10), 
        Pos Integer)

Insert Into @Temp(Id, Type, Code, Value, Pos)
          Select 1,   'ADD'       ,'S1'    ,NULL     ,NULL
Union All Select 2,   'ADD'       ,'S2'    ,NULL     ,NULL
Union All Select 3,   'DELETE'    ,'S2'    ,NULL     ,NULL
Union All Select 4,   'DELETE'    ,'S1'    ,NULL     ,NULL
Union All Select 5,   'ADD'       ,'S3'    ,NULL     ,NULL
Union All Select 6,   'ADD'       ,'S3'    ,NULL     ,NULL

Create 
Table   #Temp
       (RowId Integer Identity(1,2), 
       A_Id Integer, 
       A_Code VarChar(10), 
       B_Id Integer, 
       B_Code VarChar(10))

Insert 	
Into    #Temp(A_Id, A_Code, B_Id, B_Code)
Select  A.Id, A.Code, B.Id, B.Code 
From    @Temp A
        Inner Join @Temp B On A.Code = B.Code
Where   A.Type = 'Add'
        And B.Type = 'Delete'

Update  A
Set     Value = 'A',
        Pos = RowId
From    @Temp A
        Inner Join #Temp On A.Id = #Temp.A_Id

Update  A
Set     Value = 'B',
        Pos = RowId + 1
From    @Temp A
        Inner Join #Temp On A.Id = #Temp.B_Id

Drop Table #Temp

Select * From @Temp

-George

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