UPDATE THE TABLE - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

UPDATE THE TABLE

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-18 : 16:03:34

Hi Guys,

I have My_Table with four columns.
col1 = ID , col2 = city ,col3 = status, col4 = result
Now ID is identity column.
Cities can be as many as thousands but can be repeated also.
status can be either active or inactive.

now for each city, there can be only one active in status column,
if there exist more than one, flag the record FAIL in result else NULL.

For example, result set should be like :

1 LA Active NULL
2 LA ACTIVE FAIL
3 LA ACTIVE FAIL
4 LA INACTIVE NULL
5 LA INACTIVE NULL

There can not exist two or more actives for the same city.

Please help !!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 16:35:07

UPDATE f
SET f.Flag = CASE WHEN cnt > 1 THEN 'FAIL' ELSE NULL END
FROM (
SELECT Flag, SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY City, Status) AS cnt FROM dbo.My_Table
) AS f

N 56°04'39.26"
E 12°55'05.63"

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-18 : 18:22:56

Thanks ,

It has a little problem,
It is actually FAILing both values.
Lets get back to the sample Resultset,

The code that you provided is failing 1,2,& 3.

Please note I need to fail 2 and 3 and NULL for 1.
Thanks

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-18 : 21:03:14

Try this ....

declare @temp table(id int identity(1,1), city varchar(10), status varchar(10), d varchar(10))
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('SF', 'Inactive', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('LA', 'Active', null)

update temp set td = d from
(
select t.d td, case when t.id=c.id then null when t.status != 'Active' then null else 'Fail' end d
from @temp t join (select min(id) id, city from @temp where status='Active' group by city) c
on t.city=c.city
) temp

select * from @temp order by city

------ test result
1 LA Active NULL
2 LA Active Fail
3 LA Active Fail
4 LA Inactive NULL
5 LA Inactive NULL
9 LA Active Fail
6 SF Inactive NULL
7 SF Active NULL
8 SF Active Fail

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-18 : 21:15:12

Unfortunately, this didnt helped me. Sorry

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 23:38:53

Ben_53, why do you think my code is failing?
Using the sample data provided by namnam, my code generates this output.

declare @temp table(id int identity(1,1), city varchar(10), status varchar(10), d varchar(10))
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Active', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('LA', 'Inactive', null)
insert into @temp values('SF', 'Inactive', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('SF', 'Active', null)
insert into @temp values('LA', 'Active', null)

UPDATE f
SET f.d = CASE WHEN cnt > 1 THEN 'FAIL' ELSE NULL END
FROM (
SELECT d, SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY City, Status) AS cnt FROM @temp
) AS f

select * from @temp
/*
id	city	status		d
1	LA	Active		FAIL	-- 4
2	LA	Active		FAIL	-- 4
3	LA	Active		FAIL	-- 4
4	LA	Inactive	NULL
5	LA	Inactive	NULL
6	SF	Inactive	NULL
7	SF	Active		FAIL	-- 2
8	SF	Active		FAIL	-- 2
9	LA	Active		FAIL	-- 4
*/

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 23:41:48

Or... Do you also mean that the logical order matter?
So in my example above, row with id #9 shouldn't fail because it is a single active according to the logical order?
Even if there are three other "LA Active", they are not consecutive according to ID column, and thus the ID 9 is not failing?

And the output you want is this?


/*
id	city	status		d
1	LA	Active		FAIL	-- 3
2	LA	Active		FAIL	-- 3
3	LA	Active		FAIL	-- 3
4	LA	Inactive	NULL
5	LA	Inactive	NULL
6	SF	Inactive	NULL
7	SF	Active		FAIL	-- 2
8	SF	Active		FAIL	-- 2
9	LA	Active		NULL
*/

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 23:51:29

quote:
Originally posted by ben_53

Thanks ,

It has a little problem,
It is actually FAILing both values.
Lets get back to the sample Resultset,

The code that you provided is failing 1,2,& 3.

Please note I need to fail 2 and 3 and NULL for 1.
Thanks

Is it because Active is spellt differently? Active and ACTIVE?

N 56°04'39.26"
E 12°55'05.63"

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-30 : 15:41:35

Code :
UPDATE f
SET f.d = CASE WHEN cnt > 1 THEN 'FAIL' ELSE NULL END
FROM (
SELECT d, SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY City, Status) AS cnt FROM @temp
) AS f

is failing ALL "ACTIVE LA" however I want to keep one ACTIVE LA and fail rest.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-30 : 15:50:11

WITH CTE_Multiple AS
(SELECT ROW_NUMBER() OVER (PARTITION BY city, status
ORDER BY (SELECT 0)) RN,result
FROM my_table
)
UPDATE CTE_Multiple
SET result = 'FAIL'

This works like a charm.. Thanks everyone :)
WHERE RN<>1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-30 : 17:21:07

That will give "Inactive" as Fail too, if there are two or more "Inactive" for same city...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources