How to delete all records from or empty a custom database table?

Question

I can't seem to find a simple reliable way to do this - perhaps?:

// delete row ##
global $wpdb;
$delete = $wpdb->query( 
    $wpdb->prepare( 
         "DELETE * FROM `wp_table_name`"
    )
);

// return ##
$return = __('Table Emptied.');
if ( $wpdb->last_error ) {
    $return = $wpdb->last_error;
}

But this gives me the classic:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM wp_table_name' at line 1

Answer 1

I would just modify Q Studio example to:

global $wpdb;
$table  = $wpdb->prefix . 'table_name';
$delete = $wpdb->query("TRUNCATE TABLE $table");

Answer 2

Thanks @s_ha_dum - Truncate does the job nicely:

// delete row ##
global $wpdb;
$delete = $wpdb->query("TRUNCATE TABLE `wp_table_name`");

Answer 3

2 years late, but maybe this will help someone in the future :)

The correct syntax for deleting rows from a MySQL table is:

DELETE FROM `wp_table_name`
WHERE 1 = 1;

Answer 4

This is late and the answer been accepted. But no one mentioned that your DELETE SQL statement is incorrect. Try this:

DELETE FROM `wp_table_name`

However, this might still not work. You might need to change your MariaDB/MySQL settings, which protects such deletes, to prevent you from mistakenly deleting a full set of data. To make it work, you have to

DELETE FROM `wp_table_name` WHERE `some_column` = 'some_value'

Cheers