Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: How to update a DB2 column with a random number?

    Hi,
    Can someone please help me with this one.

    I want to update a DB2 table column (Character field) with a random number. I need a SQL for this.

    Thanks.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2004
    Posts
    7
    Thanks for the link. I checked it already. Actually I need one with UPDATE command.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    So, are you expecting someone to write you an UPDATE Statement ?

    If your employer is paying you to write this query, I think it's fair that you give a share of the money to the person who writes the update statement for you ..

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2004
    Posts
    7
    Hey, take it easy...I tried with all options and couldn't get thru. Then only I posted into this forum. My query was giving some problem. When I searched through internet, I saw this forum. I don't want anyone to do my job. Just asked for some help thinking that this forum is meant for that. Anyway thanks for your advise and help.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Post what you have tried and the error message you get ... You'll get better responses ....
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    @ nsanjeevk

    try this:

    UPDATE your.table
    SET column = CAST ( SMALLINT ( RAND () *10000 + 1 ) AS CHAR(5))
    WHERE ...

    this will set your column to a value between 1 and 10000

    ( DB2 for z/OS ).
    May be you must adjust the query if your DB2 is running on an other platform.

  8. #8
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Hi,

    On UDB we run the following to get random number..

    db2 "select SMALLINT ( RAND () *10000 + 1 ) from sysibm.sysdummy1"

    Based upon that you can write your UPDATE also.

  9. #9
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16
    Quote Originally Posted by nsanjeevk
    Hey, take it easy...I tried with all options and couldn't get thru. Then only I posted into this forum. My query was giving some problem. When I searched through internet, I saw this forum. I don't want anyone to do my job. Just asked for some help thinking that this forum is meant for that. Anyway thanks for your advise and help.
    I'm curious now... You tried with all options. What did you try?

  10. #10
    Join Date
    Nov 2004
    Posts
    7
    Hi, Appreciate all your help!!

    Initially I tried with CAST(CHAR (RAND() * 1000000000 AS CHAR)) and also tried with different CAST and RIGHT functions. It gave me -433 and -413 SQL errors. As you mentioned, I just moved AS CHAR(9) outside and its working perfect. Here is the SQL which worked in my DB2 V 7.2...

    SET Random_Num = CAST ( INT ( RAND () * 1000000000) AS CHAR(9))

    Thanks again. Have a good one!!

    Hi Sathyaram, please don't take me wrong. I just wanted to see different ideas from the forum. That's why I didn't put my SQL. Even I was not in hurry. If I give my SQL, will just get the correction to it. I won't get different ideas. Appreciate all your hardwork in this forum. Have a good one!!

Posting Permissions

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