UPDATE one table from another

[Guest_imported]

I know there has to be a simple answer to this.

I have two tables: Table A contains line-item records for a set of orders for the current year only keyed on ORDER_NUMBER. Table B has header records for ALL orders, also keyed on ORDER_NUMBER.

Table A:

ORDER_NUMBER VARCHAR2(10) 
ORDER_TYPE   VARCHAR2(4) 
LINE_ITEM    VARCHAR2(6) 
PRODUCT      VARCHAR2(10) 
QTY          FLOAT(126) 
EXT_PRICE    FLOAT(126) 
TOTAL_TAX    FLOAT(126)

Table B:

ORDER_NUMBER VARCHAR2(10) 
ORDER_TYPE   VARCHAR2(4) 
WAREHOUSE    VARCHAR2(4) 
CUST_SHIP_TO VARCHAR2(12) 
TOTAL_TAX    FLOAT(126)

I need to update records in Table A with the contents of the ORDER_TYPE and TOTAL_TAX fields in Table B. Remember, not every ORDER_NUMBER in B is in A; for every matching ORDER_NUMBER, there will be 1 to n records in A for every one in B.

How do I write a SQL*Plus UPDATE to do this?

Thanks for your help!
Lee
lee.meinhardt@smna.com

 

[THoey]

I think it will be:

UPDATE TableA
SET (TableA.Order_Type = TableB.Order_Type,
TableA.Total_Tax = TableA.Total_Tax)
WHERE TableA.Order_Number IN (
SELECT TableB.Order_Number
FROM TableB)

Off the top of my head, so it may take some tweaking... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[ija]

Hi,

Why does it look more like a school exercise than a real-life problem?
Anyway:
[tt]
update A
set (order_type, total_tax) = (
select distinct order_type, total_tax
from B
where A.order_number = B.order_number
)
where A.order_number in (
select order_number
from B
)
[/tt]
If you are not sure that [tt]"distinct order_type, total_tax"[/tt] will return only one row, add somethink like
[tt]"and rownum=1"[/tt].

Good luck...