4dkankan-center-manage/doc/update-cp-location.sql

CREATE TEMPORARY TABLE temp_table AS

SELECT s.num,c.new_num,e.location from t_scene_plus s LEFT JOIN t_scene_plus_ext e on s.id = e.plus_id
                                                      LEFT JOIN t_scene_copy_log c on c.old_num = s.num
WHERE s.rec_status = 'A' and c.id is not null and location is not null;

SELECT  location,count(1)  from temp_table GROUP BY location;


UPDATE t_scene_plus_ext  set location = 1 WHERE location is null and  plus_id in (

    SELECT id  FROM t_scene_plus WHERE num in
                                       (SELECT new_num  from temp_table WHERE location = 1)

);
UPDATE t_scene_plus_ext  set location = 3 WHERE location is null and  plus_id in (

    SELECT id  FROM t_scene_plus WHERE num in
                                       (SELECT new_num  from temp_table WHERE location = 3)

);
UPDATE t_scene_plus_ext  set location = 4 WHERE location is null and  plus_id in (

    SELECT id  FROM t_scene_plus WHERE num in
                                       (SELECT new_num  from temp_table WHERE location = 4)

);
UPDATE t_scene_plus_ext  set location = 5 WHERE location is null and  plus_id in (

    SELECT id  FROM t_scene_plus WHERE num in
                                       (SELECT new_num  from temp_table WHERE location = 5)

);
UPDATE t_scene_plus_ext  set location = 6 WHERE location is null and  plus_id in (

    SELECT id  FROM t_scene_plus WHERE num in
                                       (SELECT new_num  from temp_table WHERE location = 6)

);


DROP TABLE IF EXISTS temp_table;