Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: New to MERGE statement

    I'm a bit of a noob when it comes the SQL MERGE statement so please bear with me.

    PROBLEM: Need to update 2 values in TABLE1 to a constant value based on a field in TABLE2.

    I tried to accomplish this first with an UPDATE statement with subselects, but I couldn't get that to work correctly. Then I came accross the MERGE statement and thought I would give it a try. Here is the query I am using:

    Code:
    MERGE INTO ALTLIB.CMASAA AS AR
    USING (SELECT * FROM ALTLIB.CMASTR) AS CM
    ON (AR.CG_CUS = CM.CU_CUS AND AR.CG_CO = CM.CU_CO)
    WHEN MATCHED AND AR.CG_AGC = '1' AND AR.CG_ACT = 'Y' AND AR.CG_CO = '10' AND CM.CU_DTB < 20091001 THEN
    	UPDATE SET
    		AR.CG_ACT = 'N',
    		AR.CG_DEF = 'N'
    ELSE IGNORE;
    This statement executes without error, but reads 0 rows.

    Any advice would be appreciated.

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    is CM.CU_DTB an integer?

    reads 0 rows
    do you mean UPDATEs 0 rows?
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Oct 2012
    Posts
    3
    Yes it is an Integer value.

    The actual message says:
    Code:
    Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 1.282, SQL query: 1.282, Reading results: 0

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    But did you actually check that the rows have been updated? Since MERGE is a DML statement, not a query, it's not supposed to "return" anything except an SQLCode.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2012
    Posts
    3
    Mother Hefer!

    I know why that was happening. In my previous attempts to update, one of the commands updated all the records in that table (test table). I thought I had refreshed the data in the test library. Obviously I must have gotten sidetracked and did not. So no rows were meeting the criteria of the SELECT part of the MERGE statement.

    I changed the records and the re-ran the MERGE and what do you know.

    10:25:50 [MERGE - 1122 row(s), 1.843 secs] Command processed
    ... 1 statement(s) executed, 1122 row(s) affected, exec/fetch time: 1.843/0.000 sec [1 successful, 0 warnings, 0 errors]
    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
  •