Jump to content
Sign in to follow this  
Henry Olive

Interbase Update

Recommended Posts

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
 

 

 


 

 

 

 

Share this post


Link to post

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!

Share this post


Link to post

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

 

 

Share this post


Link to post

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.

Share this post


Link to post

What if you use a trigger BEFOREUPDATE on table Master ?

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×