Home » SQL & PL/SQL » SQL & PL/SQL » Update SQL scripts
Update SQL scripts [message #328804] Sun, 22 June 2008 23:04 Go to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Hi,

TABLE A
------------------------------------------
partno. stdprice avgprice
A null 4
B NULL 5
C NULL 10


TABLE B
-----------------------------------------------
partno. stdprice
A 2
B 3.2
C 10


Question.

How to write a scripts to update the table A for the stdprice


Result

TABLE A
-----------------------------------------------------
partno. stdprice avgprice
A 2 4
B 3.2 5
C 10 10


Thanks

Mabel
Re: Update SQL scripts [message #328805 is a reply to message #328804] Sun, 22 June 2008 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: Update SQL scripts [message #328810 is a reply to message #328804] Sun, 22 June 2008 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please 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).

Also read SQL Reference, JOINS

Regards
Michel
Re: Update SQL scripts [message #328819 is a reply to message #328810] Sun, 22 June 2008 23:35 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Pls check the attached word file. Thanks
Re: Update SQL scripts [message #328820 is a reply to message #328819] Sun, 22 June 2008 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
NGSOOKFONG wrote on Sun, 22 June 2008 21:35
Pls check the attached word file. Thanks


How does repeating previously data make things better?
Re: Update SQL scripts [message #328821 is a reply to message #328804] Sun, 22 June 2008 23:47 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
update a set a.stdprice=(select stdprice from b where a.partno=b.partno)




regards,

[Updated on: Sun, 22 June 2008 23:52]

Report message to a moderator

Re: Update SQL scripts [message #328834 is a reply to message #328821] Mon, 23 June 2008 00:47 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear sir,

How abt with subquery.

Pls refer to the attached file.

thanks

mabel
  • Attachment: UPDATE2.txt
    (Size: 0.53KB, Downloaded 93 times)
Re: Update SQL scripts [message #328835 is a reply to message #328834] Mon, 23 June 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some of us can't or don't want to download files.
Post your queries inside your topics (unless they have hundred lines).
Just use SQL formatter and follow the guidelines to post them.
You have a Test forum to test how to do it.
You have here a Preview button to check your post before posting it.

Regards
Michel
Re: Update SQL scripts [message #328844 is a reply to message #328835] Mon, 23 June 2008 01:19 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

TABLE A
-------------------------------------------
PART PO_NO PRICE

A
123 3
A
124 4
B 125 10
B 126 13
B 127 14


sQL SCRIPTS

(SELECT A.PART , MAX(A.PRICE)
FROM A
GROUP BT A.PART ) QUERY1



TABLE B
-----------------------------------------------
PART COST
A 0
B 7



Question

How to use QUERY1 TO UPDATE THE TABLE B .



RESULT

---------------------------------------

TABLE B
----------------------------------
PART COST
A
4
B
14


Re: Update SQL scripts [message #328855 is a reply to message #328844] Mon, 23 June 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can add colors, this is still not formatted.

Michel Cadot wrote on Mon, 23 June 2008 06:15
Please 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.


In addition explain with WORDS what you want not just a result.
And if you want faster answer, post a test case: create table and insert statements.

Regards
Michel

Re: Update SQL scripts [message #328864 is a reply to message #328855] Mon, 23 June 2008 02:35 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

hi,

So sorry i cann't find the space and tab function , '-' is the space. Pls help, thanks .


TABLE A
--------------------------------------
PART------------PO_NO-----------PRICE
A ------------- 123 -------------- 3
A ------------- 124 -------------- 4
B ------------- 125 -------------- 10
B ------------- 126 -------------- 13
B ------------- 127 -------------- 14


sQL SCRIPTS

(SELECT A.PART , MAX(A.PRICE)
FROM A
GROUP BT A.PART ) QUERY1



TABLE B
-----------------------------------------------
PART ------------ COST
A ----------------- 0
B ----------------- 7



Question

How to use QUERY1 TO UPDATE THE TABLE B .



RESULT

---------------------------------------

TABLE B
----------------------------------
PART ------------ COST
A --------------- 4
B --------------- 14
Re: Update SQL scripts [message #328865 is a reply to message #328864] Mon, 23 June 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you at least read the link I posted?

Regards
Michel
Re: Update SQL scripts [message #328908 is a reply to message #328865] Mon, 23 June 2008 05:20 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Buddy,

You can use the Follwoing Query For updation.

update B set B.cost = ( select Max( price)
from A
where B.part = A.part)
Re: Update SQL scripts [message #328912 is a reply to message #328908] Mon, 23 June 2008 05:28 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@panyam,

You should also read the link and learn how to format your post.

Regards
Michel
Previous Topic: Get past history data of 5 years from present data
Next Topic: tables being used in packages or procedures...........
Goto Forum:
  


Current Time: Fri Dec 02 20:50:36 CST 2016

Total time taken to generate the page: 0.50789 seconds