Got it, let's break this down for you. The wpdb->update() method works smoothly for simple equality-based WHERE clauses, but it's not ideal when you need to use MySQL functions or comparison operators like < in your condition. Here are two reliable ways to get your update done:

Recommended Approach: Use wpdb->query() with prepare() #

This method gives you full control over the SQL syntax while keeping your query secure against SQL injection. Here's the code:

// First, get the global wpdb instance
global $wpdb;

// Use the table prefix to ensure compatibility with custom prefixes
$table_name = $wpdb->prefix . 'my_table';

// Prepare the SQL query with placeholders for dynamic values
$sql = $wpdb->prepare(
    "UPDATE $table_name SET is_read = %s WHERE my_date < DATE_SUB(NOW(), INTERVAL %d DAY)",
    '1', // Value for is_read
    2    // Number of days for the interval
);

// Execute the query
$wpdb->query($sql);

Key Notes: #

• Always use $wpdb->prefix to append your table name—this avoids issues if the site uses a custom WordPress table prefix (not just wp_).
• The prepare() method sanitizes your inputs: %s is for string values, %d for integers. Even if your values are fixed right now, using prepare() is a good security habit for future changes.
• DATE_SUB(NOW(), INTERVAL 2 DAY) is a native MySQL function, so we don't need to wrap it in a placeholder—it's part of the SQL syntax itself.

Alternative: Hacking wpdb->update() (Not Recommended) #

If you really want to use wpdb->update(), you can override the WHERE clause formatting, but it's less intuitive. Here's how:

global $wpdb;
$table_name = $wpdb->prefix . 'my_table';

$wpdb->update(
    $table_name,
    array('is_read' => '1'), // Columns to update
    array('my_date < DATE_SUB(NOW(), INTERVAL 2 DAY)' => ''), // WHERE condition (hacky way)
    array('%s'), // Format for update values
    array('%s') // Format for where values (we're ignoring this since our condition is a raw clause)
);

This works because wpdb->update() will concatenate the WHERE array keys as conditions, but it's not clean—stick with the first method for readability and maintainability.

Pro Tips: #

• Always test your query on a staging site first, and back up your database before running updates.
• Double-check that your my_date column is a datetime/timestamp type—otherwise, the comparison with DATE_SUB() won't work as expected.

内容的提问来源于stack exchange，提问作者Nina