Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2012
    Posts
    9

    Unanswered: Update with Multiple Tables

    I am having trouble coming up with the SQL to update a table based on information from a different table. Each table uses a composite key and I would like to change the value of a third column on table one based on the data in a third column in Table 2.

    Attachment has sample data


    I need to update the MODE to 'I' when the PAT is 'AB'.

    Thanks for any assistance.

    Philip
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What have you tried so far? What was the result?

    Andy

  3. #3
    Join Date
    Nov 2012
    Posts
    9
    I have tried using the from clause to join the tables but DB2 doesn't support that. I also tried using a subquery to select rows from table 2 with the correct criteria but I think with the composite key I might select rows that I don't want to update.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How about supplying us with the code you are trying. We are smart, but not clairvoyant.

    Andy

  5. #5
    Join Date
    Nov 2012
    Posts
    9
    At least you are witty!

    Update Table1
    Set Table1.MODE = 'IS'
    WHERE Table1.ID = (Select Table2.ID FROM Table2 WHERE Table1.ID = Table2.ID and Table1.SECTION =Table2.SECTION and Table2.PAT = 'AB')

  6. #6
    Join Date
    Nov 2012
    Posts
    9
    Wouldn't I need to select on both key values to insure that the correct row in table1 is being updated?

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    Update Table1 as t1
    Set t1.MODE = 'IS'
    WHERE EXISTS (Select 1 FROM Table2 as T2 WHERE T1.ID = T2.ID and T.SECTION =T2.SECTION and T2.PAT = 'AB')
    Andy

  8. #8
    Join Date
    Nov 2012
    Posts
    9
    Perfect, that worked. Thanks for the help.

Tags for this Thread

Posting Permissions

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