The update statement only updates the first occurance

Question

The update statement only updates the first occurance

Karen Roslund

SSCommitted

Points: 1724
More actions
June 19, 2009 at 9:45 am

#133025

while I create my table NotifyDFS, NotifyInvestigation and POCrequested is set ''
My initial table looks as follows
IncidentID Name Date NotifyDFS NotifyInvestigation POCrequested
101 dafd 1/1/2009
102 dfdf 1/4/2009
103 dfdk 1/15/2009
and the table fitems that stores the incidentid with their items like notifyDFS(12) notifyinvestigation(13) POCrequested(14) are stored as follows
IncidentId ItemId completed
102 12 1
102 13 0
102 14 1
103 12 0
103 14 1
so if for incident 101 if any of the items exists in the fitems table i want to list them as
IncidentID Name Date NotifyDFS NotifyInvestigation POCrequested
101 dafd 1/1/2009 NA NA NA
102 dfdf 1/4/2009 yes NO Yes
103 dfdk 1/15/2009 No NA NO
So the query that i have come up with works fine but it on it only updates the value for first fitem and no the other occurance of a different fitem.. My query is as follows
[Code]
update #temptable set
Notifydfs = case when (pf.item_Id = 12 and pf.completed = 1) then 'yes'
When (pf.item_Id = 12 and pf.completed = 0 then 'No' Else 'NA' End,
NotifyInvestigation= case when (pf.item_Id = 13 and pf.completed = 1) then 'yes'
When (pf.item_Id = 13 and pf.completed = 0 then 'No' Else 'NA' End,
POCrequested = case when (pf.item_Id = 14 and pf.completed = 1) then 'yes'
When (pf.item_Id = 14 and pf.completed = 0 then 'No' Else 'NA' End
from #temptable t
Right join tbl_fitems pf on t.incident_Id = pf.Incident_Id
[/Code]
so how can i accomplish what i want to.
thanks,
Karen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DB_Newbie2007 SSCarpal Tunnel Points: 4448 More actions · Answer 1

As a general rule, I typically will run the query as a SELECT statement to ensure the records being returned (or modified) are correct before converting the query to an UPDATE or a DELETE... did you try to run this as a SELECT statement?

Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

Karen Roslund SSCommitted Points: 1724 More actions · Answer 2

yeah it is returning the data in two rows one row each for each item but i want them to be in row only...

DB_Newbie2007 SSCarpal Tunnel Points: 4448 More actions · Answer 3

The problem is you are doing a RIGHT OUTER JOIN on #temptable, so Incident_ID 101 will never get touched...

CREATE TABLE #temptable (Incident_ID int, [Name] varchar(4), Date varchar(10), NotifyDFS varchar(3), NotifyInvestigation varchar(3), POCrequested varchar(3))

INSERT INTO #temptable (Incident_ID, [Name], [Date])

SELECT '101', 'dafd', '1/1/2009' UNION ALL

SELECT '102', 'dfdf', '1/4/2009 ' UNION ALL

SELECT '103', 'dfdk', '1/15/2009'

Declare @fitems TABLE (Incident_Id int, Item_Id int, completed int)

INSERT INTO @fitems

SELECT '102', '12', '1' UNION ALL

SELECT '102', '13', '0' UNION ALL

SELECT '102', '14', '1' UNION ALL

SELECT '103', '12', '0' UNION ALL

SELECT '103', '14', '1'

select * from #temptable

update #temptable set

Notifydfs = case when (pf.item_Id = 12 and pf.completed = 1) then 'yes'

When (pf.item_Id = 12 and pf.completed = 0) then 'No'

Else 'NA' End,

NotifyInvestigation= case when (pf.item_Id = 13 and pf.completed = 1) then 'yes'

When (pf.item_Id = 13 and pf.completed = 0) then 'No'

Else 'NA' End,

POCrequested = case when (pf.item_Id = 14 and pf.completed = 1) then 'yes'

When (pf.item_Id = 14 and pf.completed = 0) then 'No'

Else 'NA' End

from #temptable t

LEFT OUTER join @fitems pf on t.incident_Id = pf.Incident_ID;

select * from #temptable;

drop table #temptable;

Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)