CODEWITHSUNDEEP
phpmysqlmysql-error-1062
jlmakes
jlmakes

Reputation: 2965

How do I delete everything from a table, except 1 entry in MySQL?

So I have an import/export module for OpenCart, but it's wiping the entire product option table before inserting new data...

I need to develop support for the 3rd party product options module I have, but in the meantime--I figure I'd just stop it from deleting an important column in my product options table.

In the product_option_value table, I have 'product_option,' 'product_id,' 'quantity' etc., and there's one column named 'info' that I want to NOT wipe. The method is below:

function storeOptionsIntoDatabase( &$database, &$options ) 
{
    // find the default language id
    $languageId = $this->getDefaultLanguageId($database);

    // start transaction, remove options
    $sql = "START TRANSACTION;\n";
    $sql .= "DELETE FROM `".DB_PREFIX."product_option`;\n";
    $sql .= "DELETE FROM `".DB_PREFIX."product_option_description` WHERE language_id=$languageId;\n";
    $sql .= "DELETE FROM `".DB_PREFIX."product_option_value`;\n";
    $sql .= "DELETE FROM `".DB_PREFIX."product_option_value_description` WHERE language_id=$languageId;\n";
    $this->import( $database, $sql );

    ...more code...
}

I'm not that familiar with MySQL, but I want something to the effect of:

$sql .= "DELETE FROM `".DB_PREFIX."product_option_value` WHERE column != 'info';\n";

Thanks!

Edit:

I tried Michael's suggestion to use UPDATE and explicitly setting them all to NULL... but that returned this error:

Error: Duplicate entry '0' for key 1 Error No: 1062 UPDATE oc_product_option_value SET product_option_value_id=NULL, product_option_id=NULL, product_id=NULL, quantity=NULL, subtract=NULL, price=NULL, prefix=NULL, sort_order=NULL, weight=NULL, sku=NULL, image=NULL

I tried taking out the primary key:

$sql .= "UPDATE ".DB_PREFIX."product_option_value SET product_option_id=NULL, product_id=NULL, quantity=NULL, subtract=NULL, price=NULL, prefix=NULL, sort_order=NULL, weight=NULL;\n";

but I get:

Error: Duplicate entry '1' for key 1 Error No: 1062 INSERT INTO `oc_product....

Edit:

Okay, so I removed the 'primary_key' field from the INSERT... and I got no error messages from the upload. But when I view a product that product options, I get this message the top of my page:

Notice: Undefined index: name in /httpdocs/ocart/catalog/model/catalog/product.php on line 418Notice: Undefined index: name in /httpdocs/ocart/catalog/model/catalog/product.php on line 418Notic.... it repeats

Upvotes: 1

Views: 1597

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270637

I'm adding a second answer, taking a completely different approach which avoids SQL problems.

  1. Export your table as a comma-separated text file. You can do this with phpmyadmin, or MySQL Workbench.
  2. Open your CSV in a spreadsheet
  3. Clear out the columns you want to clear out.
  4. Save as a new CSV
  5. Import the CSV back into your database using phpmyadmin, Workbench, or the LOAD DATA LOCAL INFILE syntax.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

Make sure I understand: You want to clear values from all columns in the table product_option_value except for the column info ? If that's what you want, then the following may work. Please don't run it before we're clear on what you're trying to do!

DELETE FROM syntax implies deleting from a table name, not a column name. What you'll need to do instead is to UPDATE your rows to set all columns except the one you intend to keep to be either NULL or empty or their default value.

Don't forget to add a WHERE condition if you need to keep some rows as they are without modifying them! Without a WHERE, this query will NULL out all columns specified in the whole table.

UPDATE product_option_value
SET 
  product_option = NULL,
  product_id = NULL,
  quantity = NULL,
  etc...
WHERE (some where condition if you need one)

Upvotes: 2

Related Questions