Update query with conditions - 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)

Update query with conditions

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-04 : 12:15:37

How to put a condition to check & apply the lowest available "priority"(Int column name) with donedate is null mark BIC=1

update TableActivities set BIC=1

Thanks for the helpful info.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 12:23:15

Could you elaborate? It isn't clear what you want. DDLs and sample data with expected output would be helpful.

Jim

Everyday I learn something that somebody else already knew

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-04 : 12:38:58

Hello Jim, Here are the details, i put in a sample data also:

How to put a condition to check & apply the lowest available "priority"(Int column name) with donedate is null mark BIC=1

conditions: based on modid, donedate is NULL and lowest available priority.

In the below case the lowest priority is 1, which is available, there is one row under priority 1, where donedate is null, mark just that row's BIC=1 & rest all ignore.

------------------

update TableActivities set BIC=1

ActivityID modid priority donedate BIC
--------------------------------------------------------------------------
23 ------------ 12 -----1 ------------- 04/26/2011 ----
34 ------------ 12 -----1 ------------- 05/27/2011 ----
63 ------------ 12 -----1 ------------- NULL ---- 1
77 ------------ 12 -----2 ------------- NULL ----
89 ------------ 12 -----2 ------------- NULL ----
90 ------------ 12 -----3 ------------- NULL ----
112 ----------- 12 -----3 ------------- NULL ----

Thank you very much for the helpful info.

wided
Posting Yak Master

218 Posts

Posted - 2011-05-04 : 12:45:29

update TableActivities set BIC=1
where donedate is null

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 12:58:56

[code]
DECLARE @TableActivities TABLE (ActivityID int,ModId int,Priority int, doneDate datetime,BIC int)--<< DDL that you should provide

INSERT INTO @TableActivities -- Sample data you should provide
SELECT 23,12 ,1,'04/26/2011',null UNION ALL
SELECT 34,12,1,' 05/27/2011',null UNION ALL
SELECT 63,12,1,NULL ,null UNION ALL
SELECT 77,12,2 , NULL ,null UNION ALL
SELECT 89,12,2 ,NULL ,null UNION ALL
SELECT 90,12 ,3, NULL,null UNION ALL
SELECT 112,12,3, NULL ,null

;with cte
AS
(
select * ,row_number() over(order by priority,isnull(donedate,'99991231') desc) as Row
from @TableActivities
)

UPDATE cte
SET BIC = 1 where row = 1

select *
from @TableActivities
[/code]

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 13:04:05

UPDATE cte
SET BIC = 1 where row = 1
and donedate is null

Jim

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-04 : 13:11:52

JIM Thank you very much it seems like working, I am confused how to use within my existing data table.

I have almost 400 modid's with 8000 rows in the table.

the example i gave is for modid = 12.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 13:15:44

change this
row_number() over(order by priority,isnull(donedate,'99991231') desc)
to
this
row_number() over(partition by modid order by priority,isnull(donedate,'99991231') desc)

Jim

Everyday I learn something that somebody else already knew

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-04 : 13:57:22

Jim, I applied this onto my database table, but noticed a problem. This script is only working with priority = 1's.

It is completely ignoring the other priorities.

Here i put in the example where all step 1 priorities has donedate filled, so it should mark BIC=1 for all Priority =2 rows.

-------------------------------------------------
DECLARE @TableActivities TABLE (ActivityID int,ModId int,Priority int, doneDate datetime,BIC int)--<< DDL that you should provide

INSERT INTO @TableActivities -- Sample data you should provide
SELECT 23,12 ,1,'04/26/2011',null UNION ALL
SELECT 34,12,1,' 05/27/2011',null UNION ALL
SELECT 63,12,1,05/28/2011 ,null UNION ALL
SELECT 77,12,2 , NULL ,null UNION ALL
SELECT 89,12,2 ,NULL ,null UNION ALL
SELECT 90,12 ,3, NULL,null UNION ALL
SELECT 112,12,3, NULL ,null UNION ALL
SELECT 123,14 ,1,'04/26/2011',null UNION ALL
SELECT 134,14,1,' 05/27/2011',null UNION ALL
SELECT 163,14,1,NULL ,null UNION ALL
SELECT 177,14,2 , NULL ,null UNION ALL
SELECT 189,14,2 ,NULL ,null UNION ALL
SELECT 190,14 ,3, NULL,null UNION ALL
SELECT 119,14,3, NULL ,null

;with cte
AS
(
select * ,row_number() over(partition by modid order by priority,isnull(donedate,'99991231') desc) as Row
from @TableActivities
)

UPDATE cte
SET BIC = 1 where row = 1
and donedate is null

select *
from @TableActivities

---------------------
Thank you very much for the helpful info.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 14:08:22

So using the aboce data set, what results are you expecting to see?

Jim

Everyday I learn something that somebody else already knew

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-04 : 14:51:12

INSERT INTO @TableActivities -- Sample data you should provide
SELECT 23,12 ,1,'04/26/2011',null UNION ALL
SELECT 34,12,1,' 05/27/2011',null UNION ALL
SELECT 63,12,1,05/28/2011 ,null UNION ALL
SELECT 77,12,2 , NULL ,null UNION ALL
SELECT 89,12,2 ,NULL ,null UNION ALL
SELECT 90,12 ,3, NULL,null UNION ALL
SELECT 112,12,3, NULL ,null UNION ALL
SELECT 123,14 ,1,'04/26/2011',null UNION ALL
SELECT 134,14,1,' '05/27/2011',null UNION ALL
SELECT 163,14,1,NULL ,null UNION ALL
SELECT 177,14,2 , NULL ,null UNION ALL
SELECT 189,14,2 ,NULL ,null UNION ALL
SELECT 190,14 ,3, NULL,null UNION ALL
SELECT 119,14,3, NULL ,null

When you use the above data, it should mark BIC = 1 for priority=2 rows where MODID=12.

The reason is all priority = 1 rows, donedate's are filled.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 14:55:02

This is my last guess, you seem unwilling to put any effort into clarifying what you want

UPDATE B
SET BIC = 1
FROM
@tableActivities B
CROSS apply
( select top 1 a.modid,a.activityID ,a.priority
from @TableActivities a
where b.modid = a.modid and a.doneDate is null
order by modid, priority asc,activityid asc

) t
WHERE b.activityid = t.activityid

select * from @tableactivities

Jim

Everyday I learn something that somebody else already knew

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-05-04 : 15:22:24

Jim, I am really sorry.

I am putting all info with conditions , now this final one seems like working. will have to go thru some more modid records to check to see how the data is updated.

Thank you very much for your help.
Best regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources