• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

SQL Update to table from data in a view not working

SQL Server 2000, SP4. Trying to get an update from one table to another to work.  Here are the players"

View:  LeadTimes
EDPNO (numeric), Days (varchar)

Table 2: VENDORITEMS
Cols EDPNO (numeric), VENDORNO (character 8), LEADTIME (character 4)
also many fields that are not related to this problem.

Table 3: ITEMMAST
Cols EDPNO (numeric), VENDORNO (character 8)

Running this 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
)

Returns this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Any help would be greatly appreciated.




Running this statement
0
macecase
Asked:
macecase
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Update V set LEADTIME = a.Lead
FROM VENDORITEMS 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
Where A.EDPNO = B.EDPNO
GROUP BY A.EDPNO, B.VENDORNO
)A
ON a.VendorNo = v.VendorNo  --- confirm this Joining condition
0
 
LowfatspreadCommented:
"basically your subquery can only return 1 column... since thats all you are updating ..."

try
Update VENDORITEMS
 set LEADTIME = X.LEAD
FROM VENDORITEMS AS v
iNNER jOIN
(
Select  A.EDPNO, B.VENDORNO,
RIGHT('0000' + LEAD,4)  AS lead
FROM (SELECT EDPNO,CONVERT(varCHAR(4),AVG(DAYS)) AS LEAD
             FROM LeadTimes
            GROUP BY EDPNO
         ) A
INNER JOIN ITEMMAST B
   ON A.EDPNO = B.EDPNO
) AS x
ON V.VENDORno=X.VENDORNO
AND V.AEDPNO=X.EDPNO


0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now