T-SQL help on updating a table - perhaps (self updation?)

Question

Post reply

T-SQL help on updating a table - perhaps (self updation?)

Guras

SSCertifiable

Points: 6518
More actions
July 20, 2011 at 12:40 pm

#241113

I have a table with the folowing data
record_id ptno Account hosp batchid v1 v2 v3 v4
221702606872217026AB070685126539G001OSX1 NULLNULLNULLNULL
221702617472217026AB070685126539G001OSX2 NULLNULLNULLNULL
221702629002217026AB070685126539G001OSX4 NULLNULLNULLNULL
22170264602217026AB070685126539G001OSX3 NULLNULLNULLNULL
Now the result needs to be just one row as follows
221702606872217026AB070685126539G001OSX1 219219219219
Basically we need to cpmpare the batchid column and update the v1,v2,v3,v4 column based on the following fliter
when batchid = G001OSX1 then v1 = 219
when batchid = G001OSX2 then v2 = 219
when batchid = G001OSX4 then v3 = 219
when batchid = G001OSX3 then v4 = 219
Any direction given will be greatly appreciated. Thank you!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

first guess:

Create Table #MySampleData

(record_id int,

ptno int,

Account varchar(30),

hosp int,

batchid varchar(30),

v1 varchar(30),

v2 varchar(30),

v3 varchar(30),

v4 varchar(30), )

INSERT INTO #MySampleData

SELECT '22170260687','2217026','AB0706851265','39','G001OSX1','NULL','NULL','NULL','NULL' UNION ALL

SELECT '22170261747','2217026','AB0706851265','39','G001OSX2','NULL','NULL','NULL','NULL' UNION ALL

SELECT '22170262900','2217026','AB0706851265','39','G001OSX4','NULL','NULL','NULL','NULL' UNION ALL

SELECT '2217026460' ,'2217026','AB0706851265','39','G001OSX3','NULL','NULL','NULL','NULL'

UPDATE #MySampleData

SET V1 = 219,

V2 = 219,

V3 = 219,

V4 = 219

WHERE batchid LIKE '%SX1'

UPDATE #MySampleData

SET V2 = 219

WHERE batchid LIKE '%SX2'

UPDATE #MySampleData

SET V3 = 219

WHERE batchid LIKE '%SX3'

UPDATE #MySampleData

SET V4 = 219

WHERE batchid LIKE '%SX4'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

SELECT

MAX(CASE when batchid = G001OSX1 then v1 = 219 ELSE NULL END) AS V1,

MAX(CASE when batchid = G001OSX2 then v2 = 219 ELSE NULL END) AS V2,

MAX(CASE when batchid = G001OSX4 then v3 = 219 ELSE NULL END) AS V3,

MAX(CASE when batchid = G001OSX3 then v4 = 219 ELSE NULL END) AS V4,

Pk Columns

FROM dbo.Table

GROUP BY Pk Columns

Use that as CTE then it's a simple update statement

Guras SSCertifiable Points: 6518 More actions · Answer 3

Thank you ALL for the tips....I went with the following solution ..worked like a charm!

SELECT hospital,account,

max(CASE when batchid = 'G001OSXG' then 219 ELSE NULL END) AS V1,

max(CASE when batchid = 'G001OSXD' then 219 ELSE NULL END) AS V2,

max(CASE when batchid = 'G001OSXF' then 219 ELSE NULL END) AS V3,

max(CASE when batchid = 'G001OSXE' then 219 ELSE NULL END) AS V4

FROM #tmpFileVersions

GROUP BY Hospital,account