Search code examples
mysqlsqljoinsql-updateinner-join

Find and replace table data with another table in MySQL

In my MySQL database, I have two tables, item and users. In the item table I have two columns called created_by and created_by_alias. The created by alias column is fully populated with names but the created_by column is empty. The next table I have is the users table. This has the id and name columns inside of it.

I would like to know whether it is possible to use MySQL to match the created_by_alias in the item table with the name column in the users table, then take the id of the user and put it into the created_by column.

I was thinking some sort of JOIN function. Any ideas would be greatly appreciated.

Solution

  • Actually, you are indeed in the right direction - MySQL has an update join syntax:

    UPDATE items
JOIN   users ON users.name = items.created_by_alias
SET    created_by = items.id