Help with MySQL Update Script

[Nich Kip]

Hi, I need some help with this MySQL Update

I've got three tables as below.

tbl1
id eid quantity
1 3 2
2 4 3

tbl2
tid eid qnty status type
1 3 1 1 1
2 3 0 1 0
3 4 1 1 1
4 4 0 1 0

tbl3
id tid type
1 1 1
2 2 0
3 3 1
4 4 0

I need an sql update to:

Find X

Find Y

Then Update status in tbl2 to 0 if X - Y > 0

X = tbl1.quantity - sum(tbl2.qnty) where tbl1.eid = tbl2.eid AND type = 1

In other words:

X = tbl1.quantity in that eid - sum(tbl2.qnty) in that eid WHERE type = 1

Thus X for eid 4 = 2 and X for eid 3 = 1

Y = SELECT COUNT(tbl3.id) FROM tbl3 WHERE tbl2.tid = tbl3.tid AND type = 0 GROUP BY tid

Thus tid 2 = 1 and tid 4 = 1

Update status in tble2 to 0 if X - Y > 0

The final result will be that the status for tbl2.tid:4 will be updated to 0 because 2 - 1 = 1

You contribution(s) are appreciated.