Interbase Update

[Henry Olive 5]

I wish everone a healthy day.

Master Table looks like below

ID.....STATUS

1..... 'Planned'

 

Detail Table looks like below

ID......ITEMNO....QTY

1........AAA..............4

1........BBB...............2

1........CCC...............1

 

ItemCard Table looks like below

ITEMNO.....RESERVED

AAA...............4

BBB................2

CCC................1

 

When i change Master Table's STATUS from 'Planned' to 'Finish' then

I want to update ItemCard table's Reserved fields (Dicrease reserved qtys as Detail table's quantities).

that is after Status =Finish then ItemCard table results should be like below

ITEMNO.....RESERVED

AAA...............0

BBB................0

CCC................0

 

Could someone please help me.

Thank You
 

 

 

 

 

 

 

[Stano 139]

UPDATE ItemCard SET RESERVED = (SELECT ItemCard .RESERVED - Detail Table.QTY FROM ....)

WHERE ITEMNO = :ITEMNO

Gradually ParamByName('ITEMNO') := AAA; BBB; CCC

All in a cycle in a single transaction!

[Henry Olive 5]

Thank you so much Stano

No need any JOIN ? ( ItemCard & DetailTable )

How can i get ITEMNO params ('AAA','BBB','CCC') ?

If you have time could you please write the SQL ?

 

Thank you

 

 

[Stano 139]

It's all homework.
Of course you need a JOIN.
You get ITEMNO params using a separate SQL. You will pass this query in a while loop, and you must update the table in each loop.
Try writing something first. Put it here and we'll comment. It won't matter if it's wrong.
If we write it to you, it will lose its effect. I don't want me to get ready-made solutions either. Only when I can't handle it.

You can put it here in pseudo code. You will have it easier. That's the way to do it.

[Serge_G 85]

What if you use a trigger BEFOREUPDATE on table Master ?

[Henry Olive 5]

Thank you so much Stano and Serge

I used Trigger as Serge's suggestion