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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.