-- Create and populate the RiskSet table. DROP TABLE IF EXISTS RiskSet; CREATE TABLE RiskSet ( rs CHAR(16), cci INTEGER, uid CHAR(16) ); INSERT INTO RiskSet VALUES ("01", 1, "245229287"), ("01", 2, "999816244"), ("01", 2, "263393391"), ("01", 2, "317518253"), ("02", 1, "797411123"), ("02", 2, "345008867"), ("02", 2, "780271095"), ("02", 2, "797852845"), ("03", 1, "648450974"), ("03", 2, "848696363"), ("03", 2, "298128928"), ("03", 2, "944555580"), ("04", 1, "828391148"), ("04", 2, "308289031"), ("04", 2, "562717440"), ("04", 2, "356491655"), ("05", 1, "613643075"), ("05", 2, "998740443"), ("05", 2, "152773022"), ("05", 2, "767643812"), ("06", 1, "379900026"), ("06", 2, "596568724"), ("06", 2, "843143574"), ("06", 2, "426011730"), ("07", 1, "600627958"), ("07", 2, "725104631"), ("07", 2, "823639313"), ("07", 2, "942882703"), ("08", 1, "243495607"), ("08", 2, "388829959"), ("08", 2, "213663006"), ("08", 2, "901825182"), ("09", 1, "868136926"), ("09", 2, "635209113"), ("09", 2, "571634821"), ("09", 2, "952546794"), ("10", 1, "478295414"), ("10", 2, "381507353"), ("10", 2, "764048172"), ("10", 2, "675718835") ; -- This first update we left join RiskSet to itself aliasing the second instance of RiskSet as 'T1'. -- -- The results are not what I expected. UPDATE RiskSet LEFT JOIN RiskSet AS T1 ON RiskSet.rs = T1.rs AND T1.cci = 1 SET RiskSet.rs = CONCAT(T1.UID,'-',RiskSet.cci) ; SELECT * FROM RiskSet; -- Clear then repopulate the RiskSet table. DELETE FROM RiskSet; INSERT INTO RiskSet VALUES ("01", 1, "245229287"), ("01", 2, "999816244"), ("01", 2, "263393391"), ("01", 2, "317518253"), ("02", 1, "797411123"), ("02", 2, "345008867"), ("02", 2, "780271095"), ("02", 2, "797852845"), ("03", 1, "648450974"), ("03", 2, "848696363"), ("03", 2, "298128928"), ("03", 2, "944555580"), ("04", 1, "828391148"), ("04", 2, "308289031"), ("04", 2, "562717440"), ("04", 2, "356491655"), ("05", 1, "613643075"), ("05", 2, "998740443"), ("05", 2, "152773022"), ("05", 2, "767643812"), ("06", 1, "379900026"), ("06", 2, "596568724"), ("06", 2, "843143574"), ("06", 2, "426011730"), ("07", 1, "600627958"), ("07", 2, "725104631"), ("07", 2, "823639313"), ("07", 2, "942882703"), ("08", 1, "243495607"), ("08", 2, "388829959"), ("08", 2, "213663006"), ("08", 2, "901825182"), ("09", 1, "868136926"), ("09", 2, "635209113"), ("09", 2, "571634821"), ("09", 2, "952546794"), ("10", 1, "478295414"), ("10", 2, "381507353"), ("10", 2, "764048172"), ("10", 2, "675718835") ; -- This second update we make a copy of RiskSet and use the copy in the UPDATE statement. -- I.e. no table aliasing in the update statement. -- -- This is the behavior I expect. DROP TEMPORARY TABLE IF EXISTS Temp_RiskSet; CREATE TEMPORARY TABLE Temp_RiskSet SELECT * FROM RiskSet; UPDATE RiskSet LEFT JOIN Temp_RiskSet ON RiskSet.rs = Temp_RiskSet.rs AND Temp_RiskSet.cci = 1 SET RiskSet.rs = CONCAT(Temp_RiskSet.UID,'-',RiskSet.cci) ; SELECT * FROM RiskSet;