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.

Please provide more info:

Give us an example table and then tell us what you want to have happen with each of your example rows

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

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.

-- Try this
USE TEMPDB

--Sample Table
Create Table dbo.rCodeTypeTest
(rID int Identity (1,1)
,rType varchar(6)
,rCode char(2)
,rValue char(1)
,rPos int
)

--Sample Date
INSERT dbo.rCodeTypeTest (rType, rCode) VALUES ('ADD' , 'S1')
INSERT dbo.rCodeTypeTest (rType, rCode) VALUES ('ADD' , 'S2')
INSERT dbo.rCodeTypeTest (rType, rCode) VALUES ('DELETE' , 'S2')
INSERT dbo.rCodeTypeTest (rType, rCode) VALUES ('DELETE' , 'S1')
INSERT dbo.rCodeTypeTest (rType, rCode) VALUES ('ADD' , 'S3')
INSERT dbo.rCodeTypeTest (rType, rCode) VALUES ('ADD' , 'S3')

SELECT * FROM dbo.rCodeTypeTest

-- Assumption: a code will be repeated only twice
-- Display what the rValue will be
SELECT rct1.rID, rct1.rType, rct1.rCode
,CASE WHEN rct1.rType = rct2.rType THEN 'X'
      WHEN rct1.rType = 'ADD' AND rct2.rType = 'DELETE' THEN 'A'
      WHEN rct2.rType = 'ADD' AND rct1.rType = 'DELETE' THEN 'B'
        ELSE NULL
    END as rValue
, rct2.rID ,rct2.rType, rct2.rCode
FROM dbo.rCodeTypeTest rct1
JOIN dbo.rCodeTypeTest rct2
    ON rct1.rCode = rct2.rCode
    AND rct1.rID != rct2.rID
WHERE rct1.rValue IS NULL
    AND rct2.rValue IS NULL
ORDER BY rct1.rCode, rValue

BEGIN TRAN -- Must Lock Table so that when we get the max PosID it won't change until we are done
-- Update the rValue
UPDATE dbo.rCodeTypeTest
    SET rValue = CASE WHEN dbo.rCodeTypeTest.rType = rct2.rType THEN 'X'
      WHEN dbo.rCodeTypeTest.rType = 'ADD' AND rct2.rType = 'DELETE' THEN 'A'
      WHEN rct2.rType = 'ADD' AND dbo.rCodeTypeTest.rType = 'DELETE' THEN 'B'
        ELSE NULL
    END
FROM dbo.rCodeTypeTest
JOIN dbo.rCodeTypeTest rct2
    ON dbo.rCodeTypeTest.rCode = rct2.rCode
    AND dbo.rCodeTypeTest.rID != rct2.rID
WHERE dbo.rCodeTypeTest.rValue IS NULL
    AND rct2.rValue IS NULL

-- Figure out Position (only necessary for SQL 2000 in SQL 2005 could use row_number function)
DECLARE @rPosID Table (rPos int Identity(1,1), rID int)

INSERT @rPosID (rID)
SELECT rID
FROM dbo.rCodeTypeTest
WHERE rPos IS NULL
ORDER BY rCode, rValue

SELECT * FROM @rPosID

DECLARE @MaxPos int
SELECT @MaxPos = ISNULL(MAX(rPos),0)
FROM dbo.rCodeTypeTest

SELECT @MaxPos + P.rPos, rct.*
FROM dbo.rCodeTypeTest rct
JOIN @rPosID P
    ON P.rID = rct.RID
ORDER BY rct.rCode, rct.rValue

UPDATE dbo.rCodeTypeTest
    SET rPos = @MaxPos + P.rPos
FROM dbo.rCodeTypeTest rct
JOIN @rPosID P
    ON P.rID = rct.RID
Commit TRAN

SELECT * FROM dbo.rCodeTypeTest
Order BY rPos


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com