DELIMITER $$


DROP PROCEDURE IF EXISTS `updateListNms`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateListNms`(IN localdb VARCHAR(50), IN centraldb VARCHAR(50), IN DMS_localdb VARCHAR(50))
BEGIN
    
    SET @getStmnt = CONCAT(
	'insert into ',localdb,'.updatelistnms (llistid)
		select a.listid
		from ',localdb,'.listnms a
		where a.listid < 0');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'update ',localdb,'.listnms a, ',localdb,'.updatelistnms b 
		set a.listid = b.listid
		where a.listid = b.llistid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'update ',localdb,'.listnms a, ',localdb,'.updatelistnms b 
		set a.lhierarchy = b.listid
		where a.lhierarchy = b.llistid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    SET @getStmnt = CONCAT(
	'update ',localdb,'.listdata a, ',localdb,'.updatelistnms b 
		set a.listid = b.listid
		where a.listid = b.llistid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'insert into ',localdb,'.updateusers (luserid)
		select a.userid
		from ',localdb,'.users a
		where a.userid < 0');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'update ',localdb,'.users a, ',localdb,'.updateusers b 
		set a.userid = b.userid
		where a.userid = b.luserid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    SET @getStmnt = CONCAT(
	'update ',DMS_localdb,'.study a, ',localdb,'.updateusers b 
		set a.userid = b.userid
		where a.userid = b.luserid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;

    SET @getStmnt = CONCAT(
	'update ',localdb,'.listnms a, ',localdb,'.updateusers b
		set a.listuid = b.userid
		where a.listuid = b.luserid');
    PREPARE stmnt1 FROM @getStmnt;
    EXECUTE stmnt1;
    DEALLOCATE PREPARE stmnt1;
    
    END$$

DELIMITER ;