TRS.WCM.Developer/贵州项目/0_update_data_relation_appendix_1.sql

DROP PROCEDURE IF EXISTS update_data_relation_appendix_1;
DELIMITER $$
CREATE PROCEDURE update_data_relation_appendix_1(
                                  IN preDocId BIGINT(20),
                                  IN appendixIdOffset BIGINT(20),
                                  IN docIdOffset BIGINT(20))
	BEGIN
    # 相关图片
    delete from relphoto_doc;
    set @p_sql = CONCAT('insert into relphoto_doc(docid, appendixids) SELECT
        a.DOCID + ',docIdOffset,',
        GROUP_CONCAT(a.MATERIALQUOTEID + ',appendixIdOffset,')
        FROM
        xwcmmaterialquote a
        LEFT JOIN xwcmmaterial b ON a.MATERIALID = b.MATERIALID
        WHERE
        b.MATERIALTYPE = 1 and a.DOCID >',preDocId,'
        GROUP BY a.DOCID;');
    prepare stmt from @p_sql;
    EXECUTE stmt;
    deallocate prepare stmt;
    #相关视频
    delete from relvideo_doc;
    set @v_sql = CONCAT('insert into relvideo_doc(docid, appendixids) SELECT
        a.DOCID + ',docIdOffset,' AS docid,
        GROUP_CONCAT(a.MATERIALQUOTEID + ',appendixIdOffset,') AS appendixids
        FROM
        xwcmmaterialquote a
        LEFT JOIN xwcmmaterial b ON a.MATERIALID = b.MATERIALID
        WHERE
        b.MATERIALTYPE = 2 and a.DOCID >',preDocId,'
        GROUP BY a.DOCID;');
    prepare stmt from @v_sql;
    EXECUTE stmt;
    deallocate prepare stmt;
  END $$
DELIMITER ;