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.
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 SETinv_sent1= '08/08/2008'WHERE inv_id IN(Select inv.inv_id,inv.inv_amtFrom ADJInvoice.dbo.INVOICE AS INV Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_IDGroup by INV.Inv_ID, inv.inv_amthaving 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 SETinv_sent1= '08/08/2008'WHERE inv_id IN(Select inv.inv_id,inv.inv_amtFrom ADJInvoice.dbo.INVOICE AS INV Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_IDwhere inv.inv_id = inv_id -- corralated sub queryGroup by INV.Inv_ID, inv.inv_amthaving Sum(Pymt_Amt)<> INV.Inv_AMT) |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 08:25:11
|
[code]UPDATE ADJInvoice.dbo.Invoice SETinv_sent1= '08/08/2008'WHERE inv_id IN(Select inv.inv_id,inv.inv_amtFrom ADJInvoice.dbo.INVOICE AS INV Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_IDGroup by INV.Inv_ID, inv.inv_amthaving Sum(Pymt_Amt)<> INV.Inv_AMT)[/code]- LumbagoMy 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 SETinv_sent1= '08/08/2008'WHERE inv_id IN(Select inv.inv_id,inv.inv_amtFrom ADJInvoice.dbo.INVOICE AS INV Left outer join ADJInvoice.dbo.PAYMENT AS PYMT ON INV.Inv_ID=PYMT.Inv_IDGroup by INV.Inv_ID, inv.inv_amthaving Sum(Pymt_Amt)<> INV.Inv_AMT) - LumbagoMy 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. |
|
|
|
|
|
|
|