Results 1 to 2 of 2

Thread: Update Query using Inner Join and Case

  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Update Query using Inner Join and Case

    Hello All – I am fairly new to this kind of programming so apologies in advance.

    I am testing some updates we will need to run on our PeopleSoft DB through TOAD. I have one table that needs to be updated, but for multiple rows with different sets of values.

    For example, TableA lists line info, and TableB is header info. I need to update a rows from TableA based on criteria from both TableA and TableB.

    Because this includes multiple rows with different values, I am trying to use the CASE method. I’m not sure if this is correct. Below is the query I have come up with so far, but I get an error about not ending properly.

    I really have 2 concerns…

    -I see that the CASE statement uses ELSE. I do not want any records not specifically mentioned in my CASE to be updated. If I leave out the ELSE argument, will only the records I specify with the WHEN, THEN be updated and everything else ignored?
    -Can the CASE statement be used with an Inner Join like this?

    Any suggestions would be helpful…thanks!


    UPDATE sysadm.TableA A
    SET A.Field1 = CASE A.Field1
    WHEN "OldValue1" THEN "NewValue1"
    WHEN "OldValue2" THEN "NewValue2"
    WHEN "OldValue3" THEN "NewValue3"
    END
    INNER JOIN sysadm.TableB B ON (A.PKCriteria1 = B.PKCriteria1 A.PKCriteria2 = B.PKCriteria2)
    WHERE A.Critera = "X"
    AND B.DATE >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
    AND B.Criteria = 'D'

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

Posting Permissions

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