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.

http://blogs.ittoolbox.com/database/...7954.asp?rss=1

Thanks for the link. I checked it already. Actually I need one with UPDATE command.

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

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.

Post what you have tried and the error message you get ... You'll get better responses ....

@ 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.

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.

I'm curious now... You tried with all options. What did you try?

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!!