Upsert a row in a DB that doesn&#t use primary keys or unique constraints

During my 7-year career as a programmer, I've interacted with SQL via an ORM most of the time. One feature from Laravel's Eloquent ORM that I find particularly useful is its updateOrInsert() method:

DB::table('posts')
    ->updateOrInsert(
        ['slug' => 'about'], // matching condition
        ['content' => 'Like and subscribe'] // created or updated values
    );

In the example above, Eloquent will look for a row in the posts table where the slug equals "about". If a row exists with that slug, Eloquent will update that row's content to "Like and subscribe". If a row doesn't exist with that slug, Eloquent will create a new row with the slug of "about" and the content of "Like and subscribe".

The problem: no primary key or unique constraints

I'm currently writing a migration script to move page data from an old WordPress site to a new WordPress site. The script connects to the database of the old site then creates an SQL file that can be imported into the database of the new site. The new site already has some pages that have been moved manually, but their content may be out-of-date. When a page already exists on the new site, we don't want to create it again: we want to update the page that's already there. We can determine if the page already exists by using the post_name column in the WordPress database, which corresponds to the page's URL slug.

If the post_name were the primary key, we could accomplish something similar to Eloquent's createOrUpdate() method using a REPLACE statement, but the post_name is not the primary key.

If the post_name had a unique constraint, we could accomplish something similar to Eloquent's createOrUpdate() method using an INSERT ... ON DUPLICATE KEY UPDATE statement, but the post_name does not have a unique constraint.

Ah, the joys of WordPress.

I looked into MySQL's IF statement, but the IF statement only works in stored procedures, triggers, and functions. I did not want to create stored procedures in the SQL file that I would import to the new site's database, so I had to search for other options.

The solution: 2 statements

The simplest solution I could find to emulating Eloquent's updateOrInsert()'s functionality in pure SQL was to break the problem into two pieces:

1. Update any existing rows with a matching slug.
2. Create a new row if there are no rows with matching slugs.

Here's what that looks like in practice:

-- Update the post if it already exists.

UPDATE wp_posts
SET post_type = 'page',
    post_title = 'About',
    post_content = 'Like and subscribe'
WHERE post_name = 'about';

-- Create a new post if it does not exist.

INSERT INTO wp_posts (post_name, post_type, post_title, post_content)
SELECT 'about', 'page', 'About', 'Like and subscribe'
WHERE NOT EXISTS (
    SELECT 1
    FROM wp_posts
    WHERE post_name = 'about'
);

NOTE: This example omits many of WordPress's required wp_posts columns for brevity and clarity.

Learning about the INSERT ... SELECT statement was the "aha" moment for me. It is intended to use the results of a query from another table to perform many inserts at once. However, you can use-and-abuse SQL's capabilities by providing your own values and only performing the insert when a post with a post_name of "about" does not exist.

Is this the most elegant and efficient solution to this problem? Probably not. But it's good enough for a one-off migration of a WordPress site, and it allows you to update or insert a row without needing stored procedures or any application logic. Hopefully this post helps you as you write powerful queries without the aid of an ORM.

The above is the detailed content of Upsert a row in a DB that doesn&#t use primary keys or unique constraints. For more information, please follow other related articles on the PHP Chinese website!