Just like my previous article on the meaning of COALESCE, the following problem is one of those brain teasers that keeps developers busy for a while, frantically searching for answers, trying and re-trying various approaches and then… well, after solving, it seems stupidly simple. This recipe will definitely help those of you tackling delicate SQL data migrations. So, here goes:
Assume your database is going through the following changes: You have a Users table, containing first and last names. You also have Accounts, which up until now only had an AccountNumber (IBAN) and an OwnerId. So now there’s this new column, called Account Nickname, where people will be able to input a friendlier name for their accounts, which is irrelevant to the bank but meaningful to the user. For the moment, you want to populate all Accounts to contain “(FirstName) (LastName)’s Bank Account”.
In some SQL flavours, you can do an UPDATE directly from a virtual table. However, in MySQL it’s a bit different:
UPDATE `accounts`, `users` SET `accounts`.`nickname` = CONCAT_WS(‘ ‘, `users`.`first_name`, `users`.`last_name`, “ ‘s Bank Account”) WHERE `accounts`.`user_id` = `users`.`id`;
Note how both the tables involved are mentioned in the first part of the UPDATE statement and what you’d usually consider JOIN criteria goes inside the WHERE. Neat, huh? Credits for the idea: http://stackoverflow.com/a/12748310/2887012.