Help with MySQL Update Script

[n_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.

[smantscheff]

My contribution is a test case which should have been prepared by you. It shows that your expectations differ from the actual results:

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);

/* find x */
select tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) as X
from tbl1, tbl2 
where tbl1.eid = tbl2.eid AND type = 1;

+-----+---+
| eid | X |
+-----+---+
|   3 | 0 |
+-----+---+
1 row in set

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

+-----+---+
| tid | Y |
+-----+---+
|   2 | 1 |
|   4 | 1 |
+-----+---+
2 rows in set

Pick it up from there and clarify what you want.

[n_kip]

Thank you for the post.

On finding X, add "GROUP BY eid", so that the script will be:

/* find x */
select tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) as X
from tbl1, tbl2
where tbl1.eid = tbl2.eid AND type = 1
GROUP BY eid;

And we'll have the needed results for X

The script for finding Y does bring the correct results

Now, the two queries will produce

X
+-----+---+
| eid | X |
+-----+---+
| 3 | 1 |
| 4 | 2 |
+-----+---+

Y
+-----+---+
| tid | Y |
+-----+---+
| 2 | 1 |
| 4 | 1 |
+-----+---+

What needs to happen is to subtract Y from X and the results should be

tid Y-X
2 0
4 1

Finally, update the status of tbl2 to 0 WHERE the result of Y-X > 0

In this case, the status in tbl2 for tid 4 will be updated to 0

All this needs to be in one script.

I appreciate you help.

Nich

[smantscheff]

How are we supposed to join the results for Y and X on tid if there is no tid field in the query for X? If they should join on x.eid=y.tid , I do not see how you get to your results, since the X result table does not contain a row with eid=2 . Also your column header for your last result table presumable should read " X-Y " instead of " Y-X ".

[n_kip]

Hi,
I though that the tid that is in tbl2 & tbl3 was for the join, and is the one that will be used to do the ultimate update of the status column in tbl2, of which I do not know how to do.
If I'm wrong, would you kindly advice?
I now see those mistakes.
Thanks.

[smantscheff]

In your query for X there has to be a field on which you can join the X value to the corresponding Y value. Without knowing the semantics of your field names it's hard to guess what you mean. In your results you show that you form the tuples

eid tid X Y X-Y
 3   2  1 1  1
 4   4  2 1  0

My question: How are we to know that eid=3 and tid=2 belong to the same tuple?

[n_kip]

Now I see what you are talking about. I was under the assumption that the tid in tbl2 and tbl3 would do the join, but now I see that they don't connect.

Basically, at the end of the day I need the status for tid in tbl2 to be updated to 0 based on X-Y > 0

Is there another way of doing this? E.g. by adding another column?
E.g.
This script:

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

Produces:

eid X tid
3 1 1
4 2 3

And this script:

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

Produces:

tid Y eid
2 1 3
4 1 4

Could the eid be used to link the two?

Thanks.

[smantscheff]

The crucial point is that you need first a joined table which you can afterwards filter some tuples from. This is what the database engine does when you SELECT something. But for this you have to design the underlying query so that it joins all tuples from which you select the desired ones.
No you are telling me that the desired tuples are

eid X Y
 3  1 1
 4  2 1

so that tid is irrelevant at this stage.
Putting al together, I get to:

select A.eid,X,Y,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

which results in

+-----+---+---+-----+
| eid | X | Y | X-Y |
+-----+---+---+-----+
|   3 | 1 | 1 | 0   |
|   4 | 2 | 1 | 1   |
+-----+---+---+-----+

Now on this query you can build your update query:

update tbl2 set status = 0 where 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 
);

after which we have table 2 as

select * from tbl2;
+-----+-----+------+--------+------+
| tid | eid | qnty | status | type |
+-----+-----+------+--------+------+
|   1 |   3 |    1 |      1 |    1 |
|   2 |   3 |    0 |      1 |    0 |
|   3 |   4 |    1 |      0 |    1 |
|   4 |   4 |    0 |      0 |    0 |
+-----+-----+------+--------+------+

[n_kip]

A BIG THANKS to you friend. I could not figure this out. You've helped me much. All that best,
Nich.

[smantscheff]

You're welcome. :)
Before you use this query in a production environment test it for performance and optimize it. I fear it can generate a heavy server load with big tables.