April 28, 2005 at 11:33 am
Can anyone think of a way to update one column when a subquery returns two values?
Here is my SQL...
UPDATE tmp_ar_exception SET count_fact =
(
SELECT DISTINCT COUNT(F_AR_Detail.date_id) AS Expr1
FROM tmp_ar_exception LEFT OUTER JOIN
F_AR_Detail ON tmp_ar_exception.dt_id = F_AR_Detail.date_id
GROUP BY F_AR_Detail.date_id
)
April 28, 2005 at 11:39 am
What is the rules that tells which row is the correct one?
April 28, 2005 at 11:44 am
I dont have a rule for one record.... I need to use both values that were returned by somehow use them one at a time to update.
April 28, 2005 at 11:44 am
Just reread the code, maybe something like this :
UPDATE AE SET AE.count_fact = dtCounts.count_fact
FROM tmp_ar_exception AE INNER JOIN
(
SELECT DISTINCT F_AR_Detail.date_id, COUNT(F_AR_Detail.date_id) AS count_fact FROM tmp_ar_exception LEFT OUTER JOIN
F_AR_Detail ON tmp_ar_exception.dt_id = F_AR_Detail.date_id
GROUP BY F_AR_Detail.date_id
) dtCounts on AE.date_id = dtCounts.date_id
April 28, 2005 at 12:09 pm
Thats really close, but It only updates one record not the two records it suppose to. Ill work on the code you supplied and see what I come up with.
April 28, 2005 at 12:10 pm
I changed it a little and it worked your a genuis coming up witht that code....thanks man
April 28, 2005 at 12:19 pm
What did you change?
April 28, 2005 at 12:24 pm
In the select statement I changed the table name where your retrieving the date.
Thanks Again
UPDATE AE SET AE.count_fact = dtCounts.count_fact
FROM #tmp_ar_exception AE INNER JOIN
(
SELECT DISTINCT #tmp_ar_exception.dt_id, COUNT(F_AR_Detail.date_id) AS count_fact FROM #tmp_ar_exception LEFT OUTER JOIN
F_AR_Detail ON #tmp_ar_exception.dt_id = F_AR_Detail.date_id
GROUP BY #tmp_ar_exception.dt_id
) dtCounts on AE.dt_id = dtCounts.dt_id
April 28, 2005 at 12:32 pm
oh ok... This is why we usually ask people to send us the real query with real tables. That way our responses work right away.
Good luck with the rest of this project.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply