PHP MYSQL UPDATE if Exist or INSERT if not?

PHP MYSQL UPDATE if Exist or INSERT if not?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

The Illusion of MySQL's IF EXISTS and the Real Solution for UPSERT Logic

A common stumbling block when dealing with legacy database logic is attempting to execute an update or insert based on whether a record exists. The code snippet in question attempts to use a SQL IF EXISTS construct within a procedural function to choose between an UPDATE and an INSERT. This approach relies on the assumption that standard SQL supports conditional execution of statements based on query results, which is not how MySQL works.

The specific example provided uses deprecated mysql_* functions alongside a flawed syntax structure:

IF EXISTS(SELECT * FROM set_colors WHERE school_art_id = '{$value}')
  UPDATE set_colors ... SELECT ...
ELSE
  INSERT INTO set_colors ... SELECT ...

There are multiple critical issues here. First, the mysql_query() function is deprecated and removed in modern PHP versions; it should be replaced with mysqli or PDO. Second, MySQL does not support an IF EXISTS block that conditionally executes two different SQL statements (UPDATE vs INSERT) within a single query execution context like this. The SELECT ... INTO or INSERT ... SELECT syntax inside the ELSE block is also syntactically incorrect for an INSERT statement in this context; you cannot simply append a SELECT clause to an INSERT without using specific keywords, and even then, the logic is flawed.

The standard solution for "update if exists or insert if not" is the UPSERT pattern. In MySQL 8.0+, you can utilize the ON DUPLICATE KEY UPDATE clause combined with a unique constraint on the school_art_id. Alternatively, the INSERT ... ON DUPLICATE KEY UPDATE syntax allows you to handle both cases in a single atomic operation.

Here is how you would refactor the logic using modern MySQL features and prepared statements to prevent SQL injection:

public function set_layer_colors($value) {
    $db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    
    // Prepare the statement
    $stmt = $db->prepare("
        INSERT INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            baseimage_id = VALUES(baseimage_id),
            sub_folder = VALUES(sub_folder),
            layer = VALUES(layer)
    ");

    // You must determine the values first (joins happen before insertion or update)
    // This requires a separate query to fetch the data from school_art and baseimage
    
    return $stmt->execute();
}

The primary pitfall in the original code is the reliance on joins within the conditional logic. You cannot dynamically switch between UPDATE and INSERT based on a subquery result easily. Instead, you should first determine if the record exists using a simple query, or better yet, rely on database constraints. If school_art_id is defined as a primary key or part of a unique index, MySQL will automatically prevent duplicates when using ON DUPLICATE KEY UPDATE.

Another consideration is transactional integrity. Since this operation involves joining three tables (set_colors, school_art, baseimage), you must ensure that the data retrieved from school_art and baseimage is valid before attempting to insert or update. If the join returns no rows, inserting a null primary key will likely violate the unique constraint immediately, which can be caught and handled gracefully.

For developers migrating this logic into modern frameworks like Laravel, you might find yourself using Eloquent's upsertOrReplace method if you are on a supported version, or wrapping the raw SQL in a transaction block to ensure consistency across the join operations. The key takeaway is that procedural conditional logic inside SQL queries is often unnecessary when database constraints and atomic update clauses can handle the state management more robustly.

Our network