Solved

Sql Server udpate with select query

Posted on 2011-09-28
2
215 Views
Last Modified: 2012-05-12
Hi,

I have the following statement and am getting an error.  I can see why there is an error, but what is the work around.  

So essentially I need to set check =1 when customernumber=realmid and the barcode =the barcode from another table...

Thanks

UPDATE Customer_Info_Barcode_Assay
SET Check1 = 1
WHERE CustomerNumber=@RealmId and Barcode=(SELECT IntuitQboInvoiceId
                                                               FROM dbo.Intuit_Staging_Invoice_w_ID
                                                               GROUP BY RealmId, IntuitQboInvoiceId)
0
Comment
Question by:joygomez
2 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 36717616
You have nothing to tie the subselect to the parent.  Try something like below.  Try below

UPDATE BA
SET Check1 = 1
from Customer_Info_Barcode_Assay BA
WHERE CustomerNumber=@RealmId
  and exists (SELECT ii.IntuitQboInvoiceId
                         FROM dbo.Intuit_Staging_Invoice_w_ID ii
                         where ii.RealmId = ba.CustomerNumber
                            and ii.IntuitOboInvoiceId = BA.Barcode)
0
 

Author Comment

by:joygomez
ID: 36717681
Great... works good...
Thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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