October 7, 2004 at 8:25 am
I know this should be simple but...
I have two tables, a feedback table and a response table. There can be multiple responses to a feedback. I realized after this DB had been in use for a while that the response that closed the feedback issue was not populating the CLOSE_DATE column of the tblFEEDBACK table.
I need to write an UPDATE query that will select the MAX RESPONSE_DATE from tblRESPONSE for each feedback issue WHERE tblFEEDBACK.FEEDBACK_ID = tblRESPONSE.FEEDBACK_ID and SET tblFEEDBACK.CLOSE_DATE = to that result.
The syntax is on the preverbial tip of my tounge but I don't know how to make it iterate for each feedback.
Your assistance is appreciated.
Sean Wyatt
Sean Wyatt
seanwyatt.com
October 7, 2004 at 9:56 am
I was able to figure it out. Here's what I used if you are interested:
DECLARE @TRACK as char(10)
DECLARE FBROW CURSOR FOR
SELECT FEEDBACK.TRACKING_ID FROM FEEDBACK WHERE FEEDBACK.STATUS = 'CLOSED'
OPEN FBROW
FETCH NEXT FROM FBROW INTO @TRACK
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE FEEDBACK
SET FEEDBACK.CLOSE_DATE = (
SELECT MAX(RESPONSE.RESP_DATE)
FROM FEEDBACK LEFT OUTER JOIN
RESPONSE ON FEEDBACK.TRACKING_ID = RESPONSE.TRACKING_ID
GROUP BY FEEDBACK.STATUS, FEEDBACK.TRACKING_ID
HAVING (FEEDBACK.TRACKING_ID = @TRACK))
WHERE FEEDBACK.TRACKING_ID = @TRACK
FETCH NEXT FROM FBROW INTO @TRACK
END
CLOSE FBROW
DEALLOCATE FBROW
Sean Wyatt
seanwyatt.com
October 7, 2004 at 1:29 pm
why do people always want to use cursors?
Embrace relational theory:
UPDATE f
SET close_date = w.maxdate
FROM feeback f
JOIN (
select feedbackid, MAX( response_date ) AS maxdate
from responses GROUP BY feedbackid
) w on w.feebackid = f.feedbackid
Remember to filter somewhere in here on which are actually supposed to be closed
October 8, 2004 at 9:19 am
Thanks John,
This is alot more concise and does use the strength of a relational DB instead of the repetitive looping of simplistic code.
I appreciate your response.
Sean Wyatt
seanwyatt.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply