Thread: sql riddle

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    Question sql riddle


    I have 2 tables the first table custAddress contains the fields recordNum,
    first name, last name, address, city, state, zip, phone
    the second table called corrected_info and contains recordNum, first name,
    last name, address, city, state, zip but no phone
    how would I update custAddress with the corrected info in the second table
    without losing the phone numbers? I need to do this in sql. if anyone has an
    idea please let me know. TIA

    shom@autographsolutions.com
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    Something like this perhaps:
    Code:
    UPDATE ca
    SET ca.first_name = ci.first_name,
           ca.last_name = ci.last_name,
           ca.address = ci.address,
           ca.city = ci.city,
           ca.state = ci.state,
           ca.zip = ci.zip
    FROM custAddress AS ca
    INNER JOIN corrected_info AS ci
    ON ca.recordNum = ci.recordNum
    DISCLAIMER: I have not actually tested the above statement. Back up your data before trying it out. Hope this helps
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo

IMN logo majestic logo threadwatch logo seochat tools logo