Hi, I need some help with this MySQL Update

I've posted this in several forums, with no avail

I've got three tables as below.

drop table if exists tbl1;
create table tbl1 (id integer, eid integer, quantity integer);
insert into tbl1 values (1,3,2),(2,4,3);

drop table if exists tbl2;
create table tbl2 (tid integer, eid integer, qnty integer, status
integer, type integer);
insert into tbl2 values
(1,3,1,1,1),(2,3,0,1,0),(3,4,1,1,1),(4,4,0,1,0);

drop table if exists tbl3;
create table tbl3 (id integer, tid integer, type integer);
insert into tbl3 values (1,1,1),(2,2,0),(3,3,1),(4,4,0);


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 in that eid - sum(tbl2.qnty) in that eid WHERE type
= 1

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

/* find x */
SELECT tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) AS X, tbl2.tid
FROM tbl1, tbl2
WHERE tbl1.eid = tbl2.eid
AND TYPE =1
GROUP BY eid

Results:
eid X tid
3 1 1
4 2 3

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

Thus tid 2 = 1 and tid 4 = 1

/* find y */
SELECT tbl2.tid, COUNT( tbl3.id ) AS Y, tbl2.eid
FROM tbl2, tbl3
WHERE tbl2.tid = tbl3.tid
AND tbl2.type =0
GROUP BY tbl2.tid

Results:
tid Y eid
2 1 3
4 1 4

Finally, update status in tble2 to 0 if X - Y > 0

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

The problem is how will X and Y be connected?

Currently, X is on tid 1 & 3, while Y is on tid 2 & 4

Can the eid be used to make the connection? If it can't, what needs to
be done in order to get the needed result?

To complete all this, how can all this be put together into one update
script?

Your help is highly appreciated

On Nov 25, 10:43*am, Nich <nich... (AT) gmail (DOT) com> wrote:
It Just got solved from one of the forums in which it was being
discussed.

update tbl2 set status = 0 where type = 0 AND eid in
(select distinct eid from
(
select A.eid, X, Y
from
(
SELECT tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) AS X
FROM tbl1, tbl2
WHERE tbl1.eid = tbl2.eid
AND TYPE =1
GROUP BY tbl1.eid
) as A
JOIN
(
SELECT COUNT( tbl3.id ) AS Y, tbl2.eid
FROM tbl2, tbl3
WHERE tbl2.tid = tbl3.tid
AND tbl2.type =0
GROUP BY tbl2.tid
) as B
ON A.eid = B.eid
HAVING X - Y > 0
) as C
);