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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-15 : 14:38:08

[code]

I need a query which should validate when there is child update status with B and update the status with A parent.

Table:Masterip
------------------------------
5.46.200.1.462222.2.1.1.1.1.1
5.46.200.1.462222.2.1.1.1.1.1.1
5.46.200.1.462222.2.1.1.1.1.1.2
5.46.200.1.462222.2.1.1.1.1.1.3
5.46.200.1.462222.2.1.1.1.1.1.4
5.46.200.1.462222.2.1.1.1.1.5
5.46.200.1.462222.2.1.1.1.1.5.1
5.46.200.1.462222.2.1.1.1.1.6

Expected output

Masterip status
------------------------------ -------------
5.46.200.1.462222.2.1.1.1.1.1 A
5.46.200.1.462222.2.1.1.1.1.1.1 B
5.46.200.1.462222.2.1.1.1.1.1.2 B
5.46.200.1.462222.2.1.1.1.1.1.3 B
5.46.200.1.462222.2.1.1.1.1.1.4 B
5.46.200.1.462222.2.1.1.1.1.5 A
5.46.200.1.462222.2.1.1.1.1.5.1 B
5.46.200.1.462222.2.1.1.1.1.6 A

Thanks for you help in advance !

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 16:10:04

[code]
UPDATE t
SET t.status= CASE WHEN Cnt > 0 THEN 'A' ELSE 'B' END
FROM table t
OUTER APPLY (SELECT COUNT(1) AS Cnt
WHERE Masterip LIKE t.Masterip + '.%'
AND Masterip <> t.Masterip
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-17 : 13:41:11

Thanks Visakh !
The query is not giving the correct expected results


DECLARE @Masterip table(
    Masterip Varchar(100) NOT NULL,
    status varchar(20) 
    );
  
 insert into @masterip
 
 select 
'5.46.200.1.462222.2.1.1.1.1.1',NULL  
union all
select  
'5.46.200.1.462222.2.1.1.1.1.1.1',NULL 
union all
select  
'5.46.200.1.462222.2.1.1.1.1.1.2',NULL
union all
select  
'5.46.200.1.462222.2.1.1.1.1.1.3',NULL 
union all
select  
'5.46.200.1.462222.2.1.1.1.1.1.4',NULL  
union all
select  
'5.46.200.1.462222.2.1.1.1.1.5',NULL 
union all
select  
'5.46.200.1.462222.2.1.1.1.1.5.1',NULL 
union all
select  
'5.46.200.1.462222.2.1.1.1.1.6',NULL



UPDATE t
SET t.status= CASE WHEN Cnt > 0 THEN 'A' ELSE 'B' END
FROM @Masterip t
OUTER APPLY (SELECT COUNT(1) AS Cnt
             WHERE Masterip LIKE t.Masterip + '.%'
             AND Masterip <> t.Masterip
             )t1
             
             
select * from @Masterip

Query results:

Masterip                       Status
----------------------------- ---
5.46.200.1.462222.2.1.1.1.1.1	B
5.46.200.1.462222.2.1.1.1.1.1.1	B
5.46.200.1.462222.2.1.1.1.1.1.2	B
5.46.200.1.462222.2.1.1.1.1.1.3	B
5.46.200.1.462222.2.1.1.1.1.1.4	B
5.46.200.1.462222.2.1.1.1.1.5	B
5.46.200.1.462222.2.1.1.1.1.5.1	B
5.46.200.1.462222.2.1.1.1.1.6	B


Expected Results:
Masterip                       Status
-----------------------------  ----
5.46.200.1.462222.2.1.1.1.1.1	A
5.46.200.1.462222.2.1.1.1.1.1.1	B
5.46.200.1.462222.2.1.1.1.1.1.2	B
5.46.200.1.462222.2.1.1.1.1.1.3	B
5.46.200.1.462222.2.1.1.1.1.1.4	B
5.46.200.1.462222.2.1.1.1.1.5	A
5.46.200.1.462222.2.1.1.1.1.5.1	B
5.46.200.1.462222.2.1.1.1.1.6	A

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-17 : 13:46:41

I was able to correct the query from table is missing..

UPDATE t
SET t.status= CASE WHEN Cnt > 0 THEN 'A' ELSE 'B' END
FROM table t
OUTER APPLY (SELECT COUNT(1) AS Cnt from table
WHERE Masterip LIKE t.Masterip + '.%'
AND Masterip <> t.Masterip
)t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 17:07:41

ok cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources