• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

Hello!

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...

UPDATE EvidPad
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
0
dbrckovi
Asked:
dbrckovi
1 Solution
 
Patrick MatthewsCommented:
If there is a one-to-one correspondence for these tables:

UPDATE EvidPad
SET EvidPad.BarCodeAso = tblGITResults.BarCodeAso, EvidPad.Barcode.Bach = tblGITResults.Barcode.Bach
FROM EvidPad, tblGITResults
WHERE EvitPad.EvitID = tblGITResults.EvitID
0
 
dbrckoviAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now