Update Issue WIth a Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Issue WIth a Subquery

Author

Topic

joesmithf1
Starting Member

6 Posts

Posted - 2012-11-14 : 18:20:01

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)

joe8079
Posting Yak Master

127 Posts

Posted - 2012-11-14 : 19:32:31

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)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-11-15 : 08:25:11

[code]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)[/code]

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

joesmithf1
Starting Member

6 Posts

Posted - 2012-11-15 : 16:08:51

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources