Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Help with Query Syntax for Update Query

I am trying to update all of one field's table to another field's data based upon a link between the two tables.:

UPDATE CustSalesByPartPastYearTemp SET CustItem = (SELECT [CUST ITEM #] FROM [INVOICE DETAILS] INNER JOIN CustSalesByPartPastYearTemp ON [INVOICE DETAILS].[LINESERIAL#] = CustSalesByPartPastYearTemp.LineSerial)

Basically, I want the field CustItem in table CustSalesByPartPastYearTemp to equal what the field CUST ITEM # in the table INVOICE DETAILS is by linking the fields LineSerial and LINESERIAL#.

This gives an error saying :Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I am attempting to run this on an .asp page, sql-2000 is the back end database.
0
dzirkelb
Asked:
dzirkelb
1 Solution
 
Dukster131Commented:
You should be able to do something like this if I understand you correctly

UPDATE  t
Set CustItem = [CUST ITEM #]
from CustSalesByPartPastYearTemp t
inner join [INVOICE DETAILS] d on t.CustItem = d.[CUST ITEM#]
INNER JOIN CustSalesByPartPastYearTemp p on p.LineSerial = d.[LINESERIAL#]
0
 
dzirkelbAuthor Commented:
Thanks.  Your post led me to the following working code:

UPDATE CustSalesByPartPastYearTemp SET CustItem = [CUST ITEM #] FROM CustSalesByPartPastYearTemp INNER JOIN [INVOICE DETAILS] on CustSalesByPartPastYearTemp.LineSerial = [INVOICE DETAILS].[LINESERIAL#]
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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