php - What should I do if UPDATE uses a subquery to update a column?
大家讲道理
大家讲道理 2017-06-05 11:09:33
0
4
796

The following sql execution is too slow, how should it be optimized?

 UPDATE AA A
     SET COL1 =
         (SELECT B.COL2
            FROM BB B
           WHERE B.BH = A.BH
           AND B.YEAR = '2016-2017'
           )
 WHERE A.YEAR = '2017-2018';

Among them, the AA table and the BB table are the same and are one table

大家讲道理
大家讲道理

光阴似箭催人老,日月如移越少年。

reply all(4)
漂亮男人

I didn’t quite understand it,
Since it’s the same step, just
update the table set column 1 = column 2 where condition
and that’s it

習慣沉默
  1. Create a new table based on the new value, then merge the two tables, and finally delete the temporary table;

  2. Create a transaction, write all updates into it, and finally commit;

某草草

What I don’t understand is whether AA and BB are one table or two tables.
If it is one table, @prolifes’ method can be used.
If it is two tables,
update AA a, BB b set a.col1 = b. col2 where a.bh = b.bh and a.year='2017-2018' and b.year='2016-2017',
Also, the slowness is related to the size of your data and the index, I just give A general method, how effective it is, you have to try it yourself

左手右手慢动作

Thank you for your suggestions. In the end, I used the one on the Internet, which is slightly more efficient:
Oracle has two methods:
Inline view update

update (
 
    select t1.id t1id ,t1.nickname t1nickname,t1.playNum t1playnum,t2.id t2id ,t2.nickname t2nickname,t2.playNum t2playnum

    from t1 inner join t2 on (t1.id=t2.id)

)

set t1nickname=t2nickname,t1playnum=t2playnum;
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template