Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: need help with multi-table update

    Hey All -

    I need to update a table from data in another table. IE:

    table A:

    name, date

    table B:

    name, date

    I need to update all the dates in table b from table a where a.name=b.name.

    Thanks in advance!

    Amy

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    UPDATE b
    SET b.date = ( SELECT date FROM a WHERE b.name = a.name )
    WHERE EXISTS ( SELECT date FROM a WHERE b.name = a.name )
    Note that there must be at most one tuple in table A for each tuple in table B. Otherwise, you will have to specify which row in A should be used to update a row in B.

    Specific products may have other facilities like MERGE statements or the ability to update something based on an updatable result set. For example:
    Code:
    UPDATE ( SELECT b.name, b.date, ( SELECT a.date FROM a WHERE a.name = b.name ) AS a_date FROM b )
    SET date = a_date
    WHERE a_date IS NOT NULL
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's your first hint Let me know how you get on!
    Code:
    UPDATE table-name SET column-name = {expression} [, column-name = {expression}] WHERE search-condition
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    amy, please do mention which database system you're using

    the update SQL is different from one to the next
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •