I have two tables: (1) Line
| ID | Client |
| --- | ------ |
| 0001| 0001 |
| 0001| 0002 |
| 0001| 0003 |
Line holds all available clients for a specific ID (there can be more IDs) and is the base table. Shipping is a derived field taken from Line_Update table (Shipping_ID field). Whenever the Client is mentioned in the Line_Update table, then Shipping value must be taken from Line_Update col:Shipping_ID. If the Client from Line table is not mentioned in Line_Update then the Shipping_ID value that belongs to Client=0000 (Line_Update table) must be assigned to the Shipping field in Line table.
- So e.g. Client:0001 is not in the Line_Update table and therefore gets assigned the Shipping value for Client=0000 from Line_update.
(2) Line_update
| ID | Client | Shipping_ID |
| --- | ------|--------------|
| 0001| 0000 | 01234 |
| 0001| 0002 | 01222 |
Line_update holds some order lines from Line table that need to overwrite the Line value whenever the Line ID is there in the Line_Update table.
(3) Expected Table
| ID | Client | Shipping |
| --- | ------ | ---------|
| 0001| 0001 | 01234 |
| 0001| 0002 | 01222 |
| 0001| 0003 | 01234 |
My question.
Does anyone know how I can create a new column in Line_update that will store the value from Shipping where ID=0000 in Line table in a new column as constant value?
What my idea was, was to use coalesce function and perform the logic that if the client is in Line_Update table then take the Shipping_ID. If it is not in Line_Update take the Shipping_ID value where Client = 0000.