[SQLize]

Home | About | FAQ | Links | Feedback

Enter MySQL script (temp tables/CRUD allowed, 5 sec / 5MB / 20 rows max):

create temporary table orders (
    order_id int
);

/* Comment out the order_id=10 andthe now_in is not updated */

insert into orders select 10;
insert into orders select 11;
insert into orders select 12;
insert into orders select 13;

create temporary table orders_delivered ( 
    orders_delivered_id int,
    orders_id int
);

insert into orders_delivered select 10, 1000;
insert into orders_delivered select 10, 1001;
insert into orders_delivered select 11, 1002;
insert into orders_delivered select 12, 1003;
insert into orders_delivered select 12, 1004;
insert into orders_delivered select 13, 1005;
insert into orders_delivered select 13, 1006;
insert into orders_delivered select 13, 1007;

create temporary table orders_delivered_sta ( 
    orders_delivered_sta_id int, 
    orders_delivered_id int,
    adate char(10),
    now_ind int
);

insert into orders_delivered_sta select 1,1000,'2011-02-11', 0;
insert into orders_delivered_sta select 2,1000,'2006-01-10', 0;
insert into orders_delivered_sta select 3,1000,'2011-09-13', 0;
insert into orders_delivered_sta select 4,1001,'2010-01-19', 0;
insert into orders_delivered_sta select 5,1001,'2011-02-21', 0;
insert into orders_delivered_sta select 6,1002,'2009-02-11', 0;
insert into orders_delivered_sta select 7,1002,'2010-08-27', 0;
insert into orders_delivered_sta select 8,1003,'2012-07-15', 0;
insert into orders_delivered_sta select 9,1004,'2007-03-09', 0;
insert into orders_delivered_sta select 10,1010,'2010-10-01', 0;
insert into orders_delivered_sta select 11,1011,'2011-03-27', 0;
insert into orders_delivered_sta select 12,1012,'2010-07-25', 0;
insert into orders_delivered_sta select 13,1013,'2004-09-18', 0;

/* I can only reference one table per query, so created temps */
create temporary table t1 select * from orders_delivered_sta;
create temporary table t2 select * from orders_delivered_sta;
create temporary table t3 select * from orders_delivered_sta;
create temporary table od1 select * from orders_delivered;
create temporary table od2 select * from orders_delivered;
create temporary table o1 select * from orders;

update t3
set t3.now_ind = 1
where t3.orders_delivered_sta_id in (
    select distinct t1.orders_delivered_sta_id from t1
    left join (
        select t2.orders_delivered_id, max(t2.adate) as MaxDate from t2
        group by t2.orders_delivered_id
    ) t2 on (t1.orders_delivered_id = t2.orders_delivered_id) and (t1.adate = t2.MaxDate)
    where t2.orders_delivered_id is not null
) and exists (
    select * from o1
    join od1 on (o1.order_id = od1.orders_delivered_id)
    where (t3.orders_delivered_id = od1.orders_id)
);

select * from t3;

| (permalink: XwFLAfHMm6) Sample 1 | Sample 2

No results are displayed if 5 second timeout reached.

orders_delivered_sta_id	orders_delivered_id	adate	now_ind
1	1000	2011-02-11	0
2	1000	2006-01-10	0
3	1000	2011-09-13	1
4	1001	2010-01-19	0
5	1001	2011-02-21	1
6	1002	2009-02-11	0
7	1002	2010-08-27	1
8	1003	2012-07-15	1
9	1004	2007-03-09	1
10	1010	2010-10-01	0
11	1011	2011-03-27	0
12	1012	2010-07-25	0
13	1013	2004-09-18	0

(13 rows returned)