March 7, 2006 at 11:18 am
Im updating table but my subquery returns more than one record.
How can I update these records.
My Sql
UPDATE tbl
SET fiscal_month_name = (select month_name
FROM tbl b, period WHERE B.dt BETWEEN period.from and period.to and B.FISCAL_DAY = 15)
March 7, 2006 at 2:59 pm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp
MSDN.com is your friend....
March 7, 2006 at 3:15 pm
Try this:
UPDATE b
SET fiscal_month_name = p.month_name
FROM tbl b
INNER JOIN period p
ON B.dt BETWEEN p.from and p.to and B.FISCAL_DAY = 15
March 8, 2006 at 1:01 am
That's definitely a possibility. With this query, however, you cannot control what fiscal_monthname will be if there is more than one (distinct) possible value. If that's a problem, you might consider doing this instead:
UPDATE tbl
SET fiscal_month_name = (select top 1 month_name
FROM tbl b, period WHERE B.dt BETWEEN period.[from] and period.[to] and B.FISCAL_DAY = 15 order by ???)
Replace ??? by an "order by" expression - e.g. "month_name", "month_name desc", "period.[from]" or whatever works in your case.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply