Updated table in the where clause in MySQL

TL;DR;

A user can have many loans. There are many users and loans in our MySQL DB. The task is to update loans.checked_at with the corresponding users.checked_at in case of the first loan record of each user.

The story

We had the following simplified MySQL database structure at work.

The original business plan was to perform a certain check for every new loan once. But for some reason, the checked_at field ended up in the users table. This seems to be a major design flaw, since every user can have many loans. Suddenly I found my brain flooded with ecstatic temptation to make some clever remark on the subject. But finally I managed to resist, so let’s take a look at the rescue mission instead:

• create a new checked_at field in the loans table
• copy the data from the old field
• ask around if anyone needs the old field
• remove the old field
• disco

The tricky part is copying the data. Thanks to the current implementation we store a timestamp for every first loan of each user. Plus this feature was not there from the beginning, so not all users have it. At the end we need to update only the first loan of a user who has the checked_at data. This way we will know which loans have been checked, when, and which ones haven’t.

Copying the data

The first approach

Let’s join the users table to loans and add a where clause to select only the first loan of each user.

UPDATE loans
       INNER JOIN users
               ON users.id = loans.user_id
SET    loans.checked_at = users.checked_at
WHERE  loans.id IN (SELECT Min(id)
                    FROM   loans
                    GROUP  BY user_id)
       AND users.checked_at IS NOT NULL;

Simple, isn’t it? But the result is an error:

Error Code: 1093. You can't specify target table 'loans' for update in FROM clause

Apparently MySQL doesn’t let you update and query the same table. At least query the same table in the where clause. Interesting…

The temptation rises up and over the threshold to implement it in some general purpose programming language like Ruby. But bear with me, being true warriors, we never give up.

The second and final approach

My good database expert friend pointed out that in fact we can try querying the loans table in an inner join.

UPDATE loans
       INNER JOIN (SELECT Min(id) AS id
                   FROM   loans
                   GROUP  BY user_id) AS loans_extract
               ON loans.id = loans_extract.id
       INNER JOIN users
               ON users.id = loans.user_id
SET    loans.checked_at = users.checked_at
WHERE  users.checked_at IS NOT NULL
       AND loans.checked_at IS NULL;

Bless his expertise, he was right and it works like charm.