How to update multiple rows with different values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to update multiple rows with different values

Author

Topic

mayank_n
Starting Member

1 Post

Posted - 2011-02-04 : 06:49:50

I have a table containing the columns Policy_Number and Rider_Code. There can be multiple records having the same Policy_Number but different Rider_Code e.g. Policy_Number 1 is associated with Rider_Code A1, A2 and A3; Policy_Number 2 has Rider_Code A2 and A3, etc. I have to update the Rider_Code column which is currently NULL with values like A1, A2, A3 if there are 3 instances of Policy_Number 1; A1, A2 if there are 2 instances of Policy_Number 2, etc.

Any help will be appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-04 : 07:48:00

Are A1, A2, A3 meant to go into 1 ROW or multiple ROWS with the same Policy_Number?

It would make more sense to have the Riders in a separate table where the combined Policy_Number (or Primary Key for that table) and each Rider_Code represents a distinct row. These tables could easily be joined on the policy_number (or Primary Key for that table), that way they could be returned by a simple query with an inner join.


Create table #policy (ID int identity(1,1),Policy_Number int not null)

INSERT INTO #policy
SELECT  100 UNION
SELECT  150  


Create table #riders (ID int identity(1,1),PolicyID int not null,Rider_Code char(2) not null)

Insert into #riders

SELECT 1,'A1' UNION
SELECT 1, 'A2' UNION
Select 1, 'A3' UNION
Select 2, 'A1' UNION
Select 2, 'A2'

Select * 
From #policy inner join #riders on #policy.ID = #riders.PolicyID

drop table #policy
drop table #riders

/*ID	Policy_Number	ID	PolicyID	Rider_Code
1	100	1	1	A1
1	100	2	1	A2
1	100	3	1	A3
2	150	4	2	A1
2	150	5	2	A2
*/

If your intent is to place all rider codes in 1 column, that is trickier ...are the rider codes in a seperate table already linked by Policy Number or anything?

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources