FB-3 Update Sql

[Henry Olive 5]

Good Morning,

 

Orders Table
ID*...DELIVERY_ID
1...........1

2...........5

 

OrderDetail Table
ID*...LINENO*...UPRICE
1..........1...................50,00
1..........2...................60,00

2..........1...................80,00

Delivery Table
ID*...ORDER_ID
1...........1

5...........2

 

Delivery_Detail Table
ID*...LINENO*..UPRICE....ORDERDETAIL_LINENO
1..........1...............55,00...........1    ( I want to update this 55,00 price to 50,00 according to OrderDetail table)
1..........2...............40,00...........2    ( I want to update this 40,00 price to 60,00 according to OrderDetail table)

5..........1...............80,00...........1

 

UPDATE DELIVERY_DETAIL DD 
  SET 
    DD.UPRICE=(SELECT UPRICE FROM ORDERDETAIL OD WHERE OD.LINENO=DD.ORDERDETAIL_LINENO)
    WHERE EXISTS (SELECT 1 FROM ORDERDETAIL OD WHERE OD.LINENO=DD.LINENO)

 

Above sql works but updates all UPrices in all deliverydetail table

I want to Update Delivery_Detail table records  just in ID=1 
 

Thank You
 

 

[Uwe Raabe 2057]

6 hours ago, Henry Olive said:

I want to Update Delivery_Detail table records  just in ID=1 

Then you should limit the WHERE clause to that condition. Currently the WHERE clause updates all records in DELIVERY_DETAIL where there exists a record in ORDERDETAIL with the same LINENO - probably that are all records.

[Henry Olive 5]

Good Morning Uwe

Thank you so much