OraFAQ Forum: SQL & PL/SQL » Sql Update stmt help

Home » SQL & PL/SQL » SQL & PL/SQL » Sql Update stmt help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql Update stmt help [message #249385]

Wed, 04 July 2007 04:19

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

Report message to a moderator

Re: Sql Update stmt help [message #249398 is a reply to message #249385]

Wed, 04 July 2007 05:44

Maaher
Messages: 7065
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

Report message to a moderator

Re: Sql Update stmt help [message #249405 is a reply to message #249385]

Wed, 04 July 2007 06:24

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

update mytable set col=3-col;

Regards
Michel

Report message to a moderator

Re: Sql Update stmt help [message #249431 is a reply to message #249405]

Wed, 04 July 2007 08:19

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

iamdurai
Messages: 96
Registered: April 2007
Location: Chennai

Member

Hi,

I think this link help you Decode Function

Reds
Thangam.

Report message to a moderator

Re: Sql Update stmt help [message #249435 is a reply to message #249433]

Wed, 04 July 2007 08:27

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Is posting a link on Google search your only answer? Laughing

In this case, this is not appropriate.

Regards
Michel

Report message to a moderator

Re: Sql Update stmt help [message #249438 is a reply to message #249385]

Wed, 04 July 2007 08:38

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Sql Update stmt help [message #249441 is a reply to message #249385]

Wed, 04 July 2007 08:44

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

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

Report message to a moderator

Re: Sql Update stmt help [message #249458 is a reply to message #249446]

Wed, 04 July 2007 09:56

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.

Report message to a moderator

Re: Sql Update stmt help [message #249550 is a reply to message #249458]

Thu, 05 July 2007 00:31

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

Report message to a moderator

Re: Sql Update stmt help [message #249558 is a reply to message #249550]

Thu, 05 July 2007 01:00

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So why didn't you post the real problem?
Are the answers given to you fit your requirements?

Regards
Michel

Report message to a moderator

Re: Sql Update stmt help [message #249565 is a reply to message #249558]

Thu, 05 July 2007 01:08

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

Report message to a moderator

Re: Sql Update stmt help [message #249567 is a reply to message #249565]

Thu, 05 July 2007 01:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Help in Preparing the query
Next Topic:	password

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 03:43:44 CST 2024