Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Update statement in db2

    Hi,

    I am trying to build an update statement in db2. I am new to this , i have a statement in sql server trying to get the same functionality in db2 also.
    statement in sql server:

    UPDATE ['+@dname+'].'+@tname+ ' SET ' + @STMT2 + '
    FROM ['+@dname+'].'+@tname+' WITH (INDEX(SUP_ITU))'+ ' WHERE SUP_ITU_id = @var1'


    this is the thing which i tried in db2:

    SET u_stmt1= ('update '|| dname ||'.'|| tname ||' SET ' || STMT2 || ' =
    (select ' || STMT2 || 'from '|| dname ||'.'|| tname || 'where SUP_ITU_id = '|| var1 ||'');
    execute immediate u_stmt1;

    please correct me, as I am missing something here

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    - in DB2 dbname should be schemaname
    - you are missing the WHERE clause for the UPDATE, so you will update all the tables rows
    - at the end you should have something like ')' instead of '')
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what is the exact error you are getting?
    Dave

  4. #4
    Join Date
    Jul 2014
    Posts
    294
    The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row..

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Even without reading the docs, you should have guessed that when you have an UPDATE like
    UPDATE someTable SET someField = (SELECT ...) WHERE someCondition
    the inner SELECT must return a single value
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply.
    I know about the error. lack of condition, it is giving multiple values . I am thinking about the (INDEX(SUP_ITU)) index . Updating through index.

    I mean updating a table based upon its index. Index was created already on that table.
    Last edited by HABBIE; 10-31-14 at 09:41.

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    That looks like a query hint - that's something you don't usually do in DB2.
    Just send the UPDATE as it is and let DB2 Query Optimizer handle it.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

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
  •