SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update Issue WIth a Subquery		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joesmithf1
Starting Member

5 Posts
Posted - 11/14/2012 :  18:20:01  Show Profile  Reply with Quote
Hi All,

I am trying to run the following query, to update the "INVOICE" table. However, i got a error saying, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I know the reason for this error is that i can only have one column in the SELECT statement of a subquery. So......my question is, how do i get around this, since i must have the "INV.Inv_AMT" column in my statement?

thank you.

UPDATE ADJInvoice.dbo.Invoice SET
inv_sent1= '08/08/2008'
WHERE inv_id IN
(Select inv.inv_id,inv.inv_amt
From ADJInvoice.dbo.INVOICE AS INV
Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_ID
Group by INV.Inv_ID, inv.inv_amt
having Sum(Pymt_Amt)<> INV.Inv_AMT)
Edited by - joesmithf1 on 11/14/2012 19:06:14

joe8079
Posting Yak Master

USA
113 Posts
Posted - 11/14/2012 :  19:32:31  Show Profile  Reply with Quote


not sure if this will work, but you might to try

UPDATE ADJInvoice.dbo.Invoice SET
inv_sent1= '08/08/2008'
WHERE inv_id IN
(Select inv.inv_id,inv.inv_amt
From ADJInvoice.dbo.INVOICE AS INV
Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_ID
where inv.inv_id = inv_id -- corralated sub query
Group by INV.Inv_ID, inv.inv_amt
having Sum(Pymt_Amt)<> INV.Inv_AMT)
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3241 Posts
Posted - 11/15/2012 :  08:25:11  Show Profile  Reply with Quote 
UPDATE ADJInvoice.dbo.Invoice SET
inv_sent1= '08/08/2008'
WHERE inv_id IN
(Select inv.inv_id,inv.inv_amt
From ADJInvoice.dbo.INVOICE AS INV 
Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_ID
Group by INV.Inv_ID, inv.inv_amt
having Sum(Pymt_Amt)<> INV.Inv_AMT)


- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

joesmithf1
Starting Member

5 Posts
Posted - 11/15/2012 :  16:08:51  Show Profile  Reply with Quote
quote:
Originally posted by Lumbago

UPDATE ADJInvoice.dbo.Invoice SET
inv_sent1= '08/08/2008'
WHERE inv_id IN
(Select inv.inv_id,inv.inv_amt
From ADJInvoice.dbo.INVOICE AS INV 
Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_ID
Group by INV.Inv_ID, inv.inv_amt
having Sum(Pymt_Amt)<> INV.Inv_AMT)


- Lumbago
My blog-> http://thefirstsql.com


Hi, Thanks! this works! but this is strange b/c i swear that i did do a test of removing "inv.inv_amt" in my previous testing, but got an error, before i go on a forum to post my issue.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000