Sql

[Henry Olive 5]

I wish everyone a healthy day.
Interbase-2007

 

OrderDetail Table

ID....LINENO...ITEMNO.....QTY...UPRICE

300.....1200.........AAA.............4...........100

300.....1201.........BBB..............5...........120

 

I made Partial Shipments in different dates.

and i changed the Order Detail UPRICE value *after transfer*

the Order into Delivery table (for currency convert issues),
 

DeliveryDetail Table

ID....LINENO....ITEMNO....QTY...UPRICE......ORDETLINENO

44........300.............AAA..........1............80..............1200

44........301.............BBB...........4............50..............1201

52........420.............AAA..........3............80..............1200

56........450.............BBB...........1............50..............1201

 

I need a SQL to update Delivery Detail Table's UPRICE datas,

according to Order Detail's UPRICE datas 

ORDETLINENO field= OderDetail's LINENO field.value

 

After SQL,  the result of Delivery Detail table's should be like below

ID....LINENO....ITEMNO....QTY...UPRICE......ORDETLINENO

44..........300...........AAA..........1............100..............1200

44..........301...........BBB...........4............120..............1201

52..........420..........AAA...........3............100..............1200

56..........450..........BBB............1............120..............1201

Could someone please help ?
Thank You

 

 

 

 

 

 

 

[Lars Fosdal 1792]

Please give your post a meaningful title.

[Guest]

maybe "some like this": (here, using FDMemTable for show as would be...  but in real case, I would need create "target" table for use "UPDATE" command from SQL, ok?

• read about your SQL Database to use:
  • UPDATE command (SET xField = xValue) with "SELECT" resulted for single "resulted value"
  • IF using a "STORED PROCEDURE" you can use/create a procedure to use "FOR SELECT" and "UPDATE" for each record
  • IF using your Delphi code, you can use "WHILE NOT EOF" in your tables and update "EDIT / POST" (at end of all updates, "COMMIT" ) your changes
• not hard, it's so easy!

select FD2.ID, FD1.PRICE

  from FDMemTable2 as FD2, FDMemTable1 as FD1   // join implicit

where FD2.ORDERTLINENO = FD1.LINETO

 

hug

prjJOIN_to_Tables_to_Update_ThirdTable_using_FDMemTables_LocalSQL_Technic.zip

Edited December 17, 2020 by Guest