Home » SQL & PL/SQL » SQL & PL/SQL » update table problem
update table problem [message #259423] Wed, 15 August 2007 10:29 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Hi to everyone
i have a problem
i have a table with username and password
some people has generated their password but i have 80k of persons without passwords (i am migrating between databases)
i was searching for a function that generate me random values and i find it
select dbms_random.string('L',12) str from dual;

i am trying to update my table doing

update table
set password= (select dbms_random.string('L',12) str from dual)
where password is null;

that sentence works BUT my problem is that ALL passwords generated are the same for all users.

Have someone any idea to resolve my problem?
thnx in advance

[Updated on: Wed, 15 August 2007 10:33]

Report message to a moderator

Re: update table problem [message #259424 is a reply to message #259423] Wed, 15 August 2007 10:38 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Try this instead.

update password
set password= dbms_random.string('L',12)
where password is null; 

Re: update table problem [message #259431 is a reply to message #259424] Wed, 15 August 2007 10:49 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Cthulhu wrote on Wed, 15 August 2007 10:38
Try this instead.

update password
set password= dbms_random.string('L',12)
where password is null; 




SQL> update cd_migra_2
set password=(dbms_random.string('L',12) str from dual where password is null); 2
set password=(dbms_random.string('L',12) str from dual where password is null)
*
ERROR at line 2:
ORA-00907: missing right parenthesis


i need to use (select dbms_random....)
Re: update table problem [message #259433 is a reply to message #259423] Wed, 15 August 2007 10:53 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Why do you need to use "select dbms_random ..." ? There is absolutely no need. You seem to be in the same sort of mindset as people who think you always have to select sysdate from dual.
Re: update table problem [message #259435 is a reply to message #259433] Wed, 15 August 2007 10:57 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Cthulhu wrote on Wed, 15 August 2007 10:53
Why do you need to use "select dbms_random ..." ? There is absolutely no need. You seem to be in the same sort of mindset as people who think you always have to select sysdate from dual.



i used without select as you suggested me, but there is a syntax problem, i thinked that maybe the problem is just the select because is the only that i take off
and of course with the select statement works perfectly
sry but i am jr, i didn't know that i can use it without the select statement
Re: update table problem [message #259436 is a reply to message #259431] Wed, 15 August 2007 10:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
update table
set password= (select dbms_random.string('L',12) str from dual)
where password is null;
If you use a sub-query then that will be executed first and then updates with that value unless it is a correlated sub-query.
Hence you are getting same values for all rows.
You should use the following code
update table
set password= dbms_random.string('L',12)
where password is null;
instead of selecting it in inner query
update table
set password= (select dbms_random.string('L',12) str from dual)
where password is null;
By
Vamsi

[Updated on: Wed, 15 August 2007 11:01]

Report message to a moderator

Re: update table problem [message #259439 is a reply to message #259436] Wed, 15 August 2007 11:02 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

vamsi kasina wrote on Wed, 15 August 2007 10:58
update table
set password= (select dbms_random.string('L',12) str from dual)
where password is null;
If you use a sub-query then that will be executed first and then updates with that value unless it is a correlated sub-query.
Hence you are getting same values for all rows.
You should use the following code
update table
set password= dbms_random.string('L',12)
where password is null;
instead of selecting it in inner query
update table
set password= (select dbms_random.string('L',12) str from dual)
where password is null;
By
Vamsi



you where right thnx a lot! that works perfectly thnx very much again Very Happy
Re: update table problem [message #259440 is a reply to message #259439] Wed, 15 August 2007 11:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You could have got this earlier itself, if you have read Cthulhu's first post. Cool

By
Vamsi
Re: update table problem [message #259441 is a reply to message #259423] Wed, 15 August 2007 11:14 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
As I said, it's the "pseudo columns must be selected from dual" mindset. I've even seen people write things like:

select 
column1,
column2,
sysdate,
...
from table, dual




Re: update table problem [message #259443 is a reply to message #259441] Wed, 15 August 2007 11:20 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Oh! I have never seen this type of query. Cool
But it(bad coding) is good to know for newbies. Razz

By
Vamsi
Previous Topic: row number count
Next Topic: V11 Documentation advice
Goto Forum:
  


Current Time: Wed Dec 07 03:13:52 CST 2016

Total time taken to generate the page: 0.07999 seconds