Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Query Syntax for Update Query

Posted on 2014-07-31
2
Medium Priority
?
244 Views
Last Modified: 2014-07-31
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
Comment
Question by:dzirkelb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Accepted Solution

by:
Dukster131 earned 2000 total points
ID: 40231783
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
 

Author Comment

by:dzirkelb
ID: 40231903
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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question