Home » SQL & PL/SQL » SQL & PL/SQL » update data according to other column (merged 6)
update data according to other column (merged 6) [message #391477] Thu, 12 March 2009 06:23 Go to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
Hi

I have a table product_details with two column
product_id and data_value

product_id data_value
2 20
3 10
5 30
6 50
8 40

I want a singel query to update product_id automatically
1 2 3 4 ................. wrt data_value column

I want
product_id data_value
1 10
2 20
3 30
4 40
5 50
Re: update data acording to other column [message #391490 is a reply to message #391477] Thu, 12 March 2009 06:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How about checking to see if your message has posted before hitting Submit again.
Re: update data according to other column (merged 5) [message #391494 is a reply to message #391477] Thu, 12 March 2009 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 27 October 2008 12:16
From your previous topic:
Michel Cadot wrote on Tue, 21 October 2008 11:41
As already requested to you:
read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and post what you tried.

Regards
Michel



In addtion, Post a Test case: create table and insert statements along with the result you want with these data.

[Updated on: Thu, 12 March 2009 06:44]

Report message to a moderator

Re: update data [message #391512 is a reply to message #391477] Thu, 12 March 2009 07:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
update product_details set
  data_value = product_id * 10;
Re: update data [message #391515 is a reply to message #391512] Thu, 12 March 2009 07:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Littlefoot wrote on Thu, 12 March 2009 18:00
update product_details set
  data_value = product_id * 10;



Hey @Littlefoot, it should be

update product_details set
  product_id = data_value / 10;

./fa/1693/0/
Cheers,
Jo
Re: update data [message #391527 is a reply to message #391477] Thu, 12 March 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

STOP posting the same question but follow the guidelines.

Regards
Michel
Re: update data [message #391536 is a reply to message #391515] Thu, 12 March 2009 08:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
joicejohn
Hey @Littlefoot, it should be
update product_details set
  product_id = data_value / 10;



Uh, oh. Right.

However, it doesn't really matter because
product_id = data_value / 10   =>   data_value = product_id * 10
./fa/917/0/
Re: update data [message #391546 is a reply to message #391536] Thu, 12 March 2009 08:52 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
./fa/449/0/ ./fa/449/0/ ./fa/449/0/

Cheers,
Jo
Re: update data according to other column (merged 6) [message #391664 is a reply to message #391477] Thu, 12 March 2009 22:29 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
the data in data_value can be any number not
10
20
30

IF IT is what will be the query

23
34
64
27
65

Re: update data according to other column (merged 6) [message #391665 is a reply to message #391477] Thu, 12 March 2009 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
select 42 from dual;
Re: update data according to other column (merged 6) [message #391667 is a reply to message #391477] Thu, 12 March 2009 22:53 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
gaikwadrachit wrote on Fri, 13 March 2009 04:32
I want a singel query to update product_id automatically

Please define "automatically". Does it mean, that when row with DATA_VALUE=38 is added, all rows with DATA_VALUE>38 shall have PRODUCT_ID incremented?
Then you are going to have a lot of trouble, as it will take a lot of time (as adding one row with smallest DATE_VALUE will lead to update of all table rows). It will also be necessary to lock the table (so multiple users will be unable to work with that table concurrently).

As desired PRODUCT_ID may be easily derived using ROW_NUMBER analytic function, there is no need to store it in the table. Create a (materialized) view to get this value, if you do not want to specify it directly in the query.
Re: update data according to other column (merged 6) [message #391701 is a reply to message #391477] Fri, 13 March 2009 02:04 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 12 March 2009 13:51

STOP posting the same question but follow the guidelines.

Regards
Michel


Next ones will be simply deleted.

Regards
Michel
Previous Topic: need help in sql query
Next Topic: Need help to write a SQL
Goto Forum:
  


Current Time: Fri Dec 09 19:16:52 CST 2016

Total time taken to generate the page: 0.21804 seconds