Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Use embedded update in select statement

    Hi I'm trying to write a query like this:

    select
    (update Table2 set Table2.Column1 = TableA.Column1)
    from TableA

    The idea is that for each row the select query returns it will update Table2 with the value from TableA.Column1.

    I was able to do it in SQL Server, however I don't know how in DB2.

    Thanks.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by subt13 View Post
    Hi I'm trying to write a query like this:

    select
    (update Table2 set Table2.Column1 = TableA.Column1)
    from TableA

    The idea is that for each row the select query returns it will update Table2 with the value from TableA.Column1.

    I was able to do it in SQL Server, however I don't know how in DB2.

    Thanks.
    Maybe in Version 11 ?
    Now most closer to your dream is MERGE statement....

    Lenny

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The idea is that for each row the select query returns it will update Table2 with the value from TableA.Column1.
    Which row(s) in Table2?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by subt13 View Post
    The idea is that for each row the select query returns it will update Table2 with the value from TableA.Column1.

    I was able to do it in SQL Server, however I don't know how in DB2.
    In standard SQL that should look something like
    Code:
    update Table2 
    set Table2.Column1 = (select TableA.Column1 from TableA where ...)
    You will need to insert the condition that correlates each row in Table2 with at most one row in TableA, of course.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2009
    Posts
    4
    Lenny, I think you hit the nail on the head. I browsed through the DB2 documentation on the merge command, but I didn't see it mention how it would handle a many to 1 or even many to many set.

    For example:
    With match you use something like this: ON ( table1.Column1 = table2.Column1 )
    what happens if Table1 and/or Table2 contain non-distinct/non-unique values that we are comparing?

  6. #6
    Join Date
    Dec 2009
    Posts
    4
    Tonkuma:

    I don't understand your question. If there are 10 rows in TableA and 10 rows in TableB, then the answer is 10 rows. Obviously I could filter it down further like with the match and its "on" keyword.

  7. #7
    Join Date
    Dec 2009
    Posts
    4
    n_i: Duh! I should have known that! That works as well, and that's probably what I was thinking of. Thank you.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by subt13 View Post
    I didn't see it mention how it would handle a many to 1 or even many to many set.
    The question really is, how YOU would handle such situations. You can always come up with an SQL statement that implements your rules, but you must have those rules in the first place.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by subt13 View Post
    Lenny, I think you hit the nail on the head. I browsed through the DB2 documentation on the merge command, but I didn't see it mention how it would handle a many to 1 or even many to many set.

    For example:
    With match you use something like this: ON ( table1.Column1 = table2.Column1 )
    what happens if Table1 and/or Table2 contain non-distinct/non-unique values that we are comparing?
    Use On(1 = 1)

    Lenny

Posting Permissions

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