Update child field with parent field if child's field is NULL

Question

I've two tables PURCHASE_ORDER_HDR and PURCHASE_ORDER_DET. Linked with key POH_ID.

Both Header and Detail table contains DELIVERYDATE where detail's Delivery Date can be null.

I want to update PURCHASE_ORDER_DET with Header's Delivery Date where Detail's Delivery Date is NULL

Tried the something like following, which is not syntactically correct.

UPDATE PURCHASE_ORDER_DET pod
INNER JOIN
(
    SELECT poh.POH_ID as parentId,
           poh.DELIVERYDATE as dlvDate
    FROM PURCHASE_ORDER_HDR poh 
) parent on pod.POH_ID = parent.parentId
SET 
    pod.DELIVERYDATE = parent.dlvDate
WHERE 
    pod.DELIVERYDATE = null

EDIT

As suggested by Mark here, I tried the following with it's equivalent MERGE statement.

UPDATE pod
SET pod.DELIVERYDATE = poh.DELIVERYDATE
FROM PURCHASE_ORDER_DET pod JOIN PURCHASE_ORDER_HDR poh ON poh.POH_ID = pod.POH_ID
WHERE pod.DELIVERYDATE = null

The equivalent MERGE statement is

MERGE INTO PURCHASE_ORDER_DET AS pod 
    USING PURCHASE_ORDER_HDR AS poh
    ON poh.POH_ID = pod.POH_ID AND pod.DELIVERYDATE = NULL 
    WHEN MATCHED THEN 
        UPDATE SET pod.DELIVERYDATE = poh.DELIVERYDATE

It does execute but no row get effected.

That is a syntactically invalid statement. Exactly what is your question? — Mark Rotteveel, Feb 15 '19 at 11:03
Maybe this question answers your question: https://stackoverflow.com/questions/24800340/update-a-table-with-join or maybe https://stackoverflow.com/questions/51318913/updating-selected-rows-in-firebird — Mark Rotteveel, Feb 15 '19 at 11:05
Thanks @MarkRotteveel . Yeah! query give syntactical error. I've also tried the `MERGE` but it doesn't do the trick. Just edited the question with my try with merge. *Exactly what is your question?* I want to update pod.deliveryDate with poh.deliveryDate if pod.deliveryDate = null — AlphaTry, Feb 15 '19 at 11:54
The condition `pod.DELIVERYDATE = null` is always false in SQL as nulls are special. You need to use `pod.DELIVERYDATE is null`. This might be why your other two attempts don't work (and otherwise, I'll give it a closer look later). — Mark Rotteveel, Feb 15 '19 at 12:08
that is why FB now has `x IS [NOT] DISTINCT FROM y` condition too :-D — Arioch 'The, Feb 15 '19 at 13:06
Also it could be wide to do divide et empera and check before change. I mean before you do `update ... where ...` do `select * ... where ...` just to see if the rows subset you would be changing is exactly what you wanted, not too much, not too few. Because stupid errors like `delete ... where x=x or y=z` are way too easy to do in SQL. In your case would you just issue `select * from pod where pod.DELIVERYDATE = null` - you would instantly see your problem has no relation to `update` and would immediately look into the source of the problem (where clause) instead of wondering where to look. — Arioch 'The, Feb 15 '19 at 13:26
@MarkRotteveel perhaps nitpicking, but it is not true that `condition pod.DELIVERYDATE = null is always false in SQL` !!! it is always `null` not always `false`. Granted, inside **simplistic** WHERE clause all that matters is that it is not `TRUE`, however under that misconception one can easily be bitten with queries like `select * from rdb$database where not (1=null)`, for example `...where ((condition_1) and (field=null)) or ((condition_2) and not (field=null))` pattern. — Arioch 'The, Feb 15 '19 at 13:34

score 1 · Accepted Answer · answered Feb 15 '19 at 12:14

1

Thanks Mark, This worked for me.

MERGE INTO PURCHASE_ORDER_DET AS pod 
    USING PURCHASE_ORDER_HDR AS poh
    ON poh.POH_ID = pod.POH_ID AND pod.DELIVERYDATE is NULL 
    WHEN MATCHED THEN 
        UPDATE SET pod.DELIVERYDATE = poh.DELIVERYDATE

answered Feb 15 '19 at 12:14

AlphaTry

475
5
27

Update child field with parent field if child's field is NULL

1 Answers1