Updating a single value in some table using value from another table

Posted on 2007-08-07
Medium Priority
Last Modified: 2013-11-05

I have two  tables: 'EvidPad' and 'tblGITResults'.
They both have these columns (among others): EvidID, BarCodeAso, BarCodeBach

I want to update EvidPad table and set values of BarCodeAso, BarCodeBach to BarCodeAso, BarCodeBach from table 'tblGITResults' where EviID is some number I specify in the variable

For example...

EvidPad contents
EvidID  BarCodeAso  BarcodeBach
1          11                  111
2          22                  222
3          33                  333

tblGitResults contents
EvidID  BarCodeAso  BarcodeBach
1          aa                  aaa
2          bb                  bbb
3          cc                  ddd

I want to update a single row in EvidPad and change values of  BarCodeAso  and BarcodeBach to the values under the same EvidID from the table tblGitResults

Example... I want to change 22 to bb and 222 to bbb

I managed to create a statement that doesn't throw an error when I script it into a stored procedure, but I'm not sure if this is the right way to do what I want.
Here's my statement...

SET BarCodeAso = BCA, BarCodeBach = BCB
FROM (SELECT TOP 1 BarCodeAso AS BCA, BarCodeBach AS BCB FROM tblGitResults WHERE EvidID = @CurrentEvid) bla
WHERE EvidID = @CurrentEvid

Is there a better way to do the same thing?
I tried using 'JOIN' but for some reason I'm getting error when I try to set aliases to table names, but I can't work without aliases becouse both tables have columns of the same name.

Thank you
Question by:dbrckovi
LVL 93

Accepted Solution

Patrick Matthews earned 800 total points
ID: 19644567
If there is a one-to-one correspondence for these tables:

SET EvidPad.BarCodeAso = tblGITResults.BarCodeAso, EvidPad.Barcode.Bach = tblGITResults.Barcode.Bach
FROM EvidPad, tblGITResults
WHERE EvitPad.EvitID = tblGITResults.EvitID
LVL 11

Author Comment

ID: 19644626
Thank you,

EvidID is unique in both tables and for every EvidID int tblGitResults, there's only one EvidID in EvidPad.

To your sollution I have added another condition under WHERE clause:
'AND EvidPad.EvidID = @CurrentEvidID' becouse I want to update only one specific row, not all of them.

I'll test it and accept the sollution if it works fine.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

807 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