Update foreign keys to one column for all records in table-CodePudding

I have the following query:

UPDATE D00EMA01.EMA_SUMO_CM_MEMBER sm
SET sm.SUBSCRIPTION_ID =
        (SELECT sb.SUBSCRIPTION_ID
         FROM D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR sb
         WHERE sb.SUBSCRIPTION_NUMBER = sm.MEMBER_NUMBER);

It is a simple update statement where I want to update the sequence PK of table EMA_SUMO_SUBSCRIPTION_MIRROR to about 300k records in table EMA_SUMO_CM_MEMBER.

Currently this column is null for all records.

The query runs for a very long time, and eventually times out, and nothing seems to be happening with a large set of rows.

What is the more performant way to get the desired result?

CodePudding user response：

Do not use a sub query, it is very slow In general, you can change and increase the time out for the execution of the query When the time out expires and the query fails, all records return to their state before the query

Try this

UPDATE  sm
SET sm.SUBSCRIPTION_ID = sb.SUBSCRIPTION_ID
from D00EMA01.EMA_SUMO_CM_MEMBER sm 
inner join D00EMA01.EMA_SUMO_SUBSCRIPTION_MIRROR sb
on sb.SUBSCRIPTION_NUMBER = sm.MEMBER_NUMBER

CodePudding user response：

How about merge?

merge into d00ema01.ema_sumo_cm_member sm
  using d00ema01.ema_sumo_subscription_mirror sb
  on (sb.subscription_number = sm.member_number)
  when matched then update set
    sm.subscription_id = sb.member_number;

Make sure sb.subscription_number and sm.member_number are indexed.