sql query help - 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

sql query help

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-18 : 09:02:48

i have a table tbl_abc having 4 cols.

ID VID CID ACTIVE
1 1 1 A
2 1 2 A
3 1 3 A
4 2 1 A
5 2 2 A
6 3 3 A
7 3 2 A
8 4 2 A
9 4 3 A
10 4 2 A

need to check combination of
vid , cid . when there is more than 1 record for any vid
then all except where cid = 1 should be active = 'I'.
when theremore than 1 record for a vid but there is no cid = 1
then update any of cid = 1 and update active = 'I' FOR all others in that group .

please help

challenge everything

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-06-18 : 11:37:35

[code]
-- *** Test Data ***
CREATE TABLE #t
(
ID int NOT NULL
,VID int NOT NULL
,CID int NOT NULL
,Active char(1) NOT NULL
)
INSERT INTO #t
SELECT 1, 1, 1, 'A'
UNION ALL SELECT 2, 1, 2, 'A'
UNION ALL SELECT 3, 1, 3, 'A'
UNION ALL SELECT 4, 2, 1, 'A'
UNION ALL SELECT 5, 2, 2, 'A'
UNION ALL SELECT 6, 3, 3, 'A'
UNION ALL SELECT 7, 3, 2, 'A'
UNION ALL SELECT 8, 4, 2, 'A'
UNION ALL SELECT 9, 4, 3, 'A'
UNION ALL SELECT 10, 4, 2, 'A'
-- *** End Test Data ***

WITH CidOrds
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY VID ORDER BY CID, ID) AS CidOrd
FROM #t
)
UPDATE CidOrds
SET Active = CASE WHEN CidOrd = 1 THEN 'A' ELSE 'I' END
,CID = CASE WHEN CidOrd = 1 AND CID <> 1 THEN CidOrd ELSE CID END
[/code]

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-18 : 23:33:36

perfect ..can i hug u ...(i means thanks )

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-18 : 23:35:13

please also suggest .. how can i learn this type of query writing..please suggest some source or examples or any way..

challenge everything

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources