asked on

How to change the value of one row per series in SQL

I get data that looks like this:

SELECT * FROM AA_CURRENT_DERIVED

MHSF_ID LAST_NAME ADMIT_DATE NEW_OR_EXISTING
------- ---------- ----------- ---------------
MHSF1 JONES 1 0
MHSF1 JONES 2 0
MHSF1 JONES 3 0
MHSF5 WU 1 0
MHSF5 WU 2 0

There are multiple rows per MHSF_ID value. For each MHSF_ID I want to change the value of NEW_OR_EXISTING to 1 for one of its rows, and keep the rest 0s.

For example, update the above to this:

MHSF_ID LAST_NAME ADMIT_DATE NEW_OR_EXISTING
------- ---------- ----------- ---------------
MHSF1 JONES 1 0
MHSF1 JONES 2 0
MHSF1 JONES 3 1
MHSF5 WU 1 0
MHSF5 WU 2 1

In the example, I'm changing the value for the row with the greatest value of the ADMIT_DATE.
(The real data would have actual datetime values for admit date, not integers like shown here)

The data can be created with this script:

CREATE TABLE [dbo].[AA_CURRENT_DERIVED](
	[MHSF_ID] [char](5) NULL,
	[LAST_NAME] [varchar](50) NULL,
	[ADMIT_DATE] [int] NULL,
	[NEW_OR_EXISTING] [smallint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 1, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 2, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF1', N'JONES', 3, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF5', N'WU', 1, 0)
GO
INSERT [dbo].[AA_CURRENT_DERIVED] ([MHSF_ID], [LAST_NAME], [ADMIT_DATE], [NEW_OR_EXISTING]) VALUES (N'MHSF5', N'WU', 2, 0)
GO

Open in new window

Haris Dulic

If you are going to match them based on the max(admit_date) then you can use this:

select SELECT a.MHSF_ID,a.LAST_NAME,a.ADMIT_DATE, case when a.ADMIT_DATE=b.ADMIT_DATE then 1 else 0 end as NEW_OR_EXISTING  
FROM AA_CURRENT_DERIVED a left join 
(select MHSF_ID, max(admit_date) as admit_Date from AA_CURRENT_DERIVED group by MHSF_ID) b on a.MHSF_ID=b.MHSF_ID

Open in new window

The code was prepared here : http://sqlfiddle.com/#!3/b86a3/2

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial