|
Subject:
|
update multiple columns of table
|
|
Posted By:
|
kingbee
|
Post Date:
|
1/21/2006 5:03:49 PM
|
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.
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/21/2006 10:41:16 PM
|
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
|
|
Reply By:
|
kingbee
|
Reply Date:
|
1/21/2006 11:18:50 PM
|
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.
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/22/2006 9:50:52 PM
|
-- 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
|
