MySQL update script to count entries

Question

I have three tables as below.

tbl1

eid qnt   pb
12     3   1
13     1   1
23     3   1

tbl2

tid   eid  fx   so
1      12    0    1
2      13    0    1
3      23    1    1
4      23    0    1

tbl3

tlid   tid  eid  fx
1      1    12    0
2      1    12    0
3      2    13    0
4      3    23    1
5      4    23    0

Need an update query that counts the number of 'tlid' in 'tbl3'

who's 'pb' in 'tb1' is '1'

who's 'eid' in 'tbl2' does not have '1' as one if it's 'fx'

and update 'so' in 'tbl2' to '0' if the total counted 'tlid' in 'tbl3' is 'LESS' than the 'qnt' in 'tbl1'

The final result will be that 'so' for 'tid 1' in 'tbl2' will be '0'

The others will not get updated because:

The sum of 'tlid' in 'tbl3' for 'tid 2' or 'eid 13' in 'tble3' = 'qnt' in 'tbl1'

and

'tlid' for 'eid 23' in 'tbl3' will not be counted becuse 'eid 23' has '1' in one of the 'fx' in 'tbl2'

Appreciate your help

drop table if exists tbl1;
create table tbl1 (eid integer, qnt integer, pb integer);
insert into tbl1 values (12,3,1),(13,1,1),(23,3,1);

drop table if exists tbl2;
create table tbl2 (tid integer, eid integer, fx integer, so integer);
insert into tbl2 values (1,12,0,1),(2,13,0,1),(3,23,1,1),(4,23,0,1);

drop table if exists tbl3;
create table tbl3 (tlid integer, tid integer, eid integer, fx integer);
insert into tbl3 values (1,1,12,0),(2,1,12,0),(3,2,13,0),(4,3,23,1),(5,4,23,0) ;

Can you please update your question with better column names? its a little hard to follow the way it is now. — The Scrum Meister
– The Scrum Meister, Commented Jan 5, 2011 at 1:18
Updated the post to reflect the right values for tbl1.eid in the insert statements. — Chandu
– Chandu, Commented Jan 5, 2011 at 1:35
Also, please note that the INSERT statements were updated to reflect the values 12,13 in tid of tbl1 instead of 2,3. — Chandu
– Chandu, Commented Jan 5, 2011 at 1:54
@The Scrum Meister Thank you for request. Would you pls advice on a naming convention that you'd prefer? Currenly they are shortened. Would like the full? E.g. for tbl1, eid = eventid, qnt = quantity and pb = published? — n_kips
– n_kips, Commented Jan 5, 2011 at 1:55

Chandu · Accepted Answer · 2011-01-05 01:34:50Z

1

Try this:

UPDATE tbl2, 
       (SELECT * 
        FROM   (SELECT tbl1.eid, 
                       tbl1.qnt, 
                       tbl3.tid, 
                       COUNT(DISTINCT tlid) cnt 
                FROM   tbl1, 
                       tbl3, 
                       tbl2 
                WHERE  tbl1.eid = tbl3.eid 
                       AND tbl2.eid = tbl3.eid 
                       AND pb = 1 
                       AND tbl2.eid NOT IN (SELECT tbl2.eid 
                                            FROM   tbl2 
                                            WHERE  fx = 1) 
                GROUP  BY tbl1.eid, 
                          tbl3.tid, 
                          tbl1.qnt) a 
        WHERE  cnt < qnt) b 
SET    so = 0 
WHERE  tbl2.tid = b.tid

answered Jan 5, 2011 at 1:34

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

n_kips Over a year ago

Hi, thank you Cybernate for your responce. Have runned the script, it does not bring any errors, but it does not update 'so' for 'tid 1' in 'tbl2' to be '0'. It says "0 row(s) affected" Any idea? Is it working on your side?

Chandu Over a year ago

It does update the so =1 for tid=1. I think you picked up the query before I updated a typo. Can you copy the query again and try?

n_kips Over a year ago

By the way, how do you get proficient in being able to write such a query so fast? I can do only simple queries.

Collectives™ on Stack Overflow

MySQL update script to count entries

Appreciate your help

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Appreciate your help

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related