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

Posted on 2007-08-07
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 92

    Accepted Solution

    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
    LVL 11

    Author Comment

    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

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now