Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: selective update master table from another table

    Hi, I'm new here...
    I normally use a procedural language to do this, but I'd like to know if (and how) SQL can do the same.

    Master table has something like:
    Key Value
    1 100
    2 200
    3 300
    4 400

    Transaction has something like:
    Key NewValue
    2 22
    4 44
    (those are simplified - real tables have a lot more data)

    When I apply the transactions I want to change the master Value column to be whatever is in NewValue but *only* if the keys match - otherwise I don't want the value to change at all.

    I've tried writing update with a sub-query to the transactions, but it overwrites all the non-matching rows with nulls!
    For example:
    update master M
    set value = (select newvalue
    from trans T
    where M.key = T.key)

    No SQL tutorial I can find seems to provide any such example - am I asking the impossible?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Standard SQL does not support join operations in an UPDATE statement, although many of the products add this ability as a "superset" feature (above and beyond standard SQL).

    Although I'd probably be a lightweight and use the non-standard extension, I could do this using standard SQL like:
    Code:
    UPDATE master
       SET value = (SELECT newvalue
          FROM trans AS T
          WHERE T.key = master.key)
       WHERE EXISTS (SELECT *
          FROM trans AS z
          WHERE  z.key = master.key)
    -PatP

  3. #3
    Join Date
    Nov 2004
    Posts
    2

    Smile thanks!

    Thanks Pat, that does *exactly* what I need - especially as it's generic SQL, I need it to be portable.
    Now to find out how well it performs compared with the procedural language approach!
    - Steve (puzzzled)

Posting Permissions

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