Home » SQL & PL/SQL » SQL & PL/SQL » Sql Update stmt help
Sql Update stmt help [message #249385] Wed, 04 July 2007 04:19 Go to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi

I have a following data in table test

col1
0
0
0
1
1
1
2
2
2
3
3
3

i want to update the data and it should look like this

3
3
3
2
2
2
1
1
1
0
0
0

thanks in advance
nusrat
Re: Sql Update stmt help [message #249398 is a reply to message #249385] Wed, 04 July 2007 05:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
OLD NEW
0 -> 3
0 -> 3
0 -> 3
1 -> 2
1 -> 2
1 -> 2
2 -> 1
2 -> 1
2 -> 1
3 -> 0
3 -> 0
3 -> 0


That doesn't make sense. The data in the table is the same. Oracle doesn't know order in its storage. If you want to get your data out in an ordered matter, then you should apply an order by in your select. So, no can do.

MHE
Re: Sql Update stmt help [message #249405 is a reply to message #249385] Wed, 04 July 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update mytable set col=3-col;

Regards
Michel
Re: Sql Update stmt help [message #249431 is a reply to message #249405] Wed, 04 July 2007 08:19 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Thanks Michel

Its working

Thanks and regards
Nusrat

[Updated on: Wed, 04 July 2007 08:19]

Report message to a moderator

Re: Sql Update stmt help [message #249433 is a reply to message #249385] Wed, 04 July 2007 08:23 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,


I think this link help you Decode Function


Reds
Thangam.
Re: Sql Update stmt help [message #249435 is a reply to message #249433] Wed, 04 July 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is posting a link on Google search your only answer? Laughing
In this case, this is not appropriate.

Regards
Michel
Re: Sql Update stmt help [message #249438 is a reply to message #249385] Wed, 04 July 2007 08:38 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

your exactly correct just i gave the tips.why i gave like that means, he/she want to know about decode function thats why.

This is ans:

" update tab_name
set col1=decode(col1,'0',1,'1',2,'2',3,'3',0); "


Reds
Thangam.

[Updated on: Wed, 04 July 2007 08:40]

Report message to a moderator

Re: Sql Update stmt help [message #249439 is a reply to message #249438] Wed, 04 July 2007 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, this is another way to do it.

When you have a precise answer (here decode), post a link to the documentation instead of Google that will return many useless links (1,560,000 in your last link).

Regards
Michel
Re: Sql Update stmt help [message #249441 is a reply to message #249385] Wed, 04 July 2007 08:44 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hai Michel,

I apologies for this, Here after I will take care of about this.

Reds
Thangam.

[Updated on: Wed, 04 July 2007 08:45]

Report message to a moderator

Re: Sql Update stmt help [message #249446 is a reply to message #249438] Wed, 04 July 2007 09:21 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
hi
this is also working ,but there is a problem in it.
if i m having 1..100 then it will be little bit painful
" update tab_name
set col1=decode(col1,'0',1,'1',2,'2',3,'3',0); "

Thanks
Nusrat


Re: Sql Update stmt help [message #249458 is a reply to message #249446] Wed, 04 July 2007 09:56 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,

" update tab_name set col1=null where col1=100; "
" Update tab_name set col1=col1+ where col1<100; "
" update tab_name set col1=0 where col1 is null; "

Do like this.

and i want ask some information about this
1) For what purpose your using this?


Reds
Thangam.
Re: Sql Update stmt help [message #249550 is a reply to message #249458] Thu, 05 July 2007 00:31 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi

Thanks for your help.
There was a requirement to update the 'From Date' and 'To Date' to match the data with privious year.

Thanks

Nusrat
Re: Sql Update stmt help [message #249558 is a reply to message #249550] Thu, 05 July 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why didn't you post the real problem?
Are the answers given to you fit your requirements?

Regards
Michel
icon7.gif  Re: Sql Update stmt help [message #249565 is a reply to message #249558] Thu, 05 July 2007 01:08 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi

Yes

I posted the problem in a simplest way
coz it was easy for me to explain and also to get a quick soluiton .

Thanks

Nusrat
Re: Sql Update stmt help [message #249567 is a reply to message #249565] Thu, 05 July 2007 01:14 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most often dates are different than numbers so maybe the solution could be different.
But if it is OK for you, it is OK for me.

Regards
Michel
Previous Topic: Help in Preparing the query
Next Topic: password
Goto Forum:
  


Current Time: Thu Dec 08 18:14:03 CST 2016

Total time taken to generate the page: 0.05585 seconds