multiple column subquery for update statement in mysql

Question

I have the following update statement

UPDATE mytable
    set tmax = (
                SELECT tmax
                FROM myvalues
                WHERE Longitude_MAX >= v_lng and 
                      STR_TO_DATE(Date, '%m/%d/%Y') = v_date
                      limit 1),
        tmin = (
                SELECT tmin
                FROM myvalues
                WHERE Longitude_MAX >= v_lng and 
                      STR_TO_DATE(Date, '%m/%d/%Y') = v_date
                      limit 1),    
    where pkid = v_pkid;

its working but very slow, can we optimize it?

there are some example on net, but these join the both tables and have no such join.

what JOIN? Do I need to join both tables?

Muhammad
– Muhammad

2014-03-22 09:25:23 +00:00
Commented Mar 22, 2014 at 9:25 — Muhammad
– Muhammad, Commented Mar 22, 2014 at 9:25
You need to join one table to the other table

Strawberry
– Strawberry

2014-03-22 09:27:07 +00:00
Commented Mar 22, 2014 at 9:27 — Strawberry
– Strawberry, Commented Mar 22, 2014 at 9:27

echo_Me · Accepted Answer · 2014-03-22 10:22:03Z

2

Try that with JOIN

   UPDATE mytable  mt
   INNER JOIN myvalues mv
   ON STR_TO_DATE(mv.Date, '%m/%d/%Y') = mt.accident_dt
   SET mt.tmax = mv.tmax , mt.tmin=mv.tmin
   WHERE Longitude_MAX >= v_lng 
   and STR_TO_DATE(Date, '%m/%d/%Y') = v_date
   AND pkid = v_pkid;

edited Mar 22, 2014 at 10:22

answered Mar 22, 2014 at 9:30

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

6 Comments

Muhammad Over a year ago

dear echo_Me "ON mv.tmax = mt.tmax" the mt.tmax is null, so this will not update the table.

Muhammad Over a year ago

I think only mt.transaction_date and mv.date can be join.

echo_Me Over a year ago

you right, then you need to make ON condition with the relation between the two tables.

echo_Me Over a year ago

you have no relation between tables ?

Muhammad Over a year ago

Before edit the above answer solve the problem, I changed the ON to ON STR_TO_DATE(mv.Date, '%m/%d/%Y') = mt.accident_dt UPDATE mytable mt INNER JOIN myvalues mv ON STR_TO_DATE(mv.Date, '%m/%d/%Y') = mt.accident_dt SET mt.tmax = mv.tmax , mt.tmin=mv.tmin WHERE Longitude_MAX >= v_lng and STR_TO_DATE(Date, '%m/%d/%Y') = v_date AND pkid = v_pkid;

|

Collectives™ on Stack Overflow

multiple column subquery for update statement in mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related