Find and Copy Data from Another Table

  • uknites

    SSC Veteran

    Points: 255

    Hi:

    I want to update DOB in Customers table from Info Table

    Two tables

    Customers

    ID, Name, DOB, Blood Type

    Info

    ID, DOB

    Scenario

    ========

    Find where ID of “Customers” = ID of “Info” then copy “DOB” from Info to “DOB” Customers

    Note: there is data already in DOB of Customers, i want to update it from the data in Info

    Thanks

  • scdecade

    SSChasing Mays

    Points: 641

    update c
    set
    dob=i.dob
    from
    customers c
    join
    info i on c.id=i.id;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • uknites

    SSC Veteran

    Points: 255

    sorry i am not able to decode c and customers c?

    info i ?

    can you please clerify

    thanks a lot

  • scdecade

    SSChasing Mays

    Points: 641

    Sorry I didn't look to see this was about Access.  The following should work as a Pass-Through query:

    UPDATE Items INNER JOIN Customers ON Items.ID = Customers.ID SET Customers.DOB = [Items].[DOB];

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • uknites

    SSC Veteran

    Points: 255

    thanks alot 🙂

    It worked 🙂

  • Jeff Moden

    SSC Guru

    Points: 995608

    uknites wrote:

    thanks alot 🙂

    It worked 🙂

    Excellent.  My next question would be... why are you denormalizing/duplicating data?  For that matter, why does the INFO table have a DOB column to begin with?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 8920

    You never read the part of the start of every SQL forum is posted for the last 30 years about providing DDL when you post, did you? You also believe that there is such a thing as a generic "id", generic "name" and you're not willing to put the constraint on blood types (the only column whose name you got right). And you do not provde sample data.

    CREATE TABLE Customers

    (customer_id CHAR(16) NOT NULL PRIMARY KEY,

    customer_name NVARCHAR(35) NOT NULL,

    birth_date DATE, -- note the NULL!

    blood_type CHAR(2) NOT NULL

    CHECK (blood_type IN ('A', 'B', 'AB', 'O')),

    ..);

    I would have left the birth date as a null. Otherwise, there are some problems with doing an update off of the second table.

    CREATE TABLE Customer_Corrections

    (customer_id CHAR(16) NOT NULL PRIMARY KEY,

    birth_date DATE NOT NULL,

    >> Find where ID of “Customers” = ID [sic] of “Info” then copy “birth_date” from Info to “birth_date” Customers <<

    I hope you understand that "customers" is the name of the table, not a column, and that you simply screwed up. This is a simple use of the merge statement, which you should Google.

    MERGE INTO Customers AS Target

    USING Customer_Corrections AS Source

    ON Target.customer_id = Source.customer_id

    WHEN MATCHED

    THEN Target.birth_date = Source.birth_date;

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • WILLIAM MITCHELL

    SSChampion

    Points: 13668

    Jeff, it sounds like the OP has a permanent table Customers, and Info is a staging table that has updates & corrections.

    Joe, to quote from Kenneth Fisher's blog yesterday "Is it really that hard to be polite, kind and respectful of those around you?"

  • Jeff Moden

    SSC Guru

    Points: 995608

    WILLIAM MITCHELL wrote:

    Jeff, it sounds like the OP has a permanent table Customers, and Info is a staging table that has updates & corrections.

    Understood.  The OP said as much in the last line of his original post.  I'm hoping to hear from the OP because this just doesn't sound right.  I've never heard of anyone having to update all of the DOBs for all the entries in a Customer table.  Something else is wrong and the OP isn't telling us about that.  If he did, we might be able to help prevent the need for doing such a thing in the future.

    For example, did someone try to update the DOB for a single customer and forget a WHERE clause and they restored a copy of the database get a copy of the Customer table so that they could update the original?  That would also let us know that a simple full table UPDATEN would need to be done rather than a merge.  It would also be nice to known if there are any triggers on the Customer table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply