October 5, 2007 at 10:02 am
Hello,
I am having problems updating my table ITEMMAST from a view LeadTime. Here is the SQL Statement:
Update VENDORITEMS set LEADTIME =
(
Select A.EDPNO, B.VENDORNO,
substring('0000',1, 4- Len(cast(AVG(A.Days)as char(4)))) + cast(AVG(A.Days)as char(4)) AS lead
FROM LeadTimes A
INNER JOIN ITEMMAST B
ON A.EDPNO = B.EDPNO
Where A.EDPNO = B.EDPNO
GROUP BY A.EDPNO, B.VENDORNO
)
Running this statement returns
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
What am I doing wroing?
October 5, 2007 at 12:20 pm
Your inner query isn't related to the outer query in any way, and you have 3 columns instead of one in the sub-query.
You need to use something like this (the join criteria isn't clear, so I'm guesstimating):
update V
set v.leadtime=l.lead
from vendoritem v inner join
(
Select A.EDPNO, B.VENDORNO,
substring('0000',1, 4- Len(cast(AVG(A.Days)as char(4)))) + cast(AVG(A.Days)as char(4)) AS lead
FROM LeadTimes A
INNER JOIN ITEMMAST B
ON A.EDPNO = B.EDPNO
GROUP BY A.EDPNO, B.VENDORNO
) l
on v.edpno=l.edpno and v.vendorno=l.vendorno
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply