WPDB Insert or if exists Update

Question

I'm not crazy familiar with WPDB or SQL in general but I have a custom table for my project and I'm trying to assign some metadata to it. What I'd "like" to happen is if a row exists, update it and if not insert it. I've read both Insert and Update in the WPDB Codex but neither really went into an "either or" situation. I thought i could work with update, so my code so far looks like this:

$wpdb->update(
    $wpdb->prepare(
        $wpdb->prefix.'item_info',
        array(
            'post_id'       => $post_id,
            'item_stock'    => $item_stock
        ),
        array('post_id' => $post_id)
    )
);

Does WordPress have anything like an "IF exists Update, ELSE Insert", or do I need to run custom SQL to achieve this, or do I need to query the database first to see if an ID exists in my table THEN decide whether to update it or insert it?

Answer 1

First, you are using prepare incorrectly. You seem to have $wpdb->update's arguments wrapped in $wpdb->prepare like that. That won't work. In effect, you are passing update a single argument-- the output of prepare. Try something simple like the following and you will see why that won't work:

$post_id = 123;
$item_stock = 567;
var_dump(
  $wpdb->prepare(
    $wpdb->prefix.'item_info',
    array(
        'post_id'       => $post_id,
        'item_stock'    => $item_stock
    ),
    array('post_id' => $post_id)
  )
);

And $wpdb->update() runs prepare for you.

Second, if this were me, I skip the helper function bloat and write a proper ON DUPLICATE KEY UPDATE query:

$sql = "INSERT INTO {$wpdb->prefix}item_info (post_id,item_stock) VALUES (%d,%s) ON DUPLICATE KEY UPDATE item_stock = %s";
// var_dump($sql); // debug
$sql = $wpdb->prepare($sql,$post_id,$item_stock,$item_stock);
// var_dump($sql); // debug
$wpdb->query($sql);

This assumes that post_id is a UNIQUE index or PRIMARY KEY. If your table structure is what I think it is, let the database handle it.

Answer 2

Have you tried $wpdb->replace. According to WP Codex:

Replace a row in a table if it exists or insert a new row in a table if the row did not already exist.

I have tried myself in some plugins and it does the work when trying to avoid unique IDs duplication errors, etc.

More info in the codex

Answer 3

You can try and update the table using $wpdb->update, I am using the id in the following code, but you can use any criteria.

$result = $wpdb->update($tableName, $info, array('id' => $info["id"]));
//If nothing found to update, it will try and create the record.
if ($result === FALSE || $result < 1) {
    $wpdb->insert($tableName, $info);
}

Update() result output

• $result === FALSE : Fail
• $result === 0 : Success, but nothing updated
• $result > 1: Success and updated

Answer 4

use $wpdb->insert, $wpdb->insert can insert and update too.. IN 2023

EDIT $wpdb->insert not update the row , use $wpdb->replace

Answer 5

You should check if the row exists first.

Most likely you'll want to try to get the ID or primary key for the row you're trying to update, then $wpdb->update if it does or $wpdb->insert of it doesn't