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.