How to bulk edit OpenCart products without breaking your catalogue

What OpenCart 4 gives you out of the box

Less than most people expect. The product list under Catalog → Products has checkboxes, and the two things those checkboxes do are copy and delete. There is no bulk edit, no inline price column you can type into, and no way to apply one change to a selection.

There is also no importer. The tools OpenCart 4 ships under System → Maintenance are backup, error log, notifications, upgrade and uploads. The backup tool exports tables as SQL and restores SQL. People do use that as an import route, and it is the single most expensive mistake in this guide.

So the real choice is between four routes, and they differ mostly in how much they let you see before the damage is done.

Route one: the product form, four hundred times

It works. It is correct by construction, because it is the same code path the software uses for everything else: every table gets written, every language row gets written, the SEO keyword gets written, and the cache gets cleared.

It costs a day, and the failure mode is not corruption but fatigue: product three hundred and eleven gets the cost typed into the price field, and nobody notices for a month. If the change is small, do this. Most changes people reach for a tool for are under thirty products.

Route two: SQL against the database

This is the one that looks cheapest and is not. The reason is that a product in OpenCart is not one row. It is a row in oc_product plus rows in roughly twenty other tables, and which of them your change has to touch depends on what you are changing.

Changes that are usually safe as SQL

• Price, cost, quantity, status, date available. All of these live on oc_product itself. An UPDATE that moves a price is a one-liner, and the worst thing it does is move the wrong price.

Changes that are not

• Name, description, meta title, tags. These live in oc_product_description, one row per product per language. A store with four languages has four rows to keep in step, and an update that hits one of them leaves the other three saying the old thing.
• Model, SKU, UPC, EAN, JAN, ISBN, MPN. These moved. On OpenCart 4.0.2.0 they are columns on oc_product; on 4.1 they are rows in oc_product_code. A statement written against one release silently updates nothing on the other: no rows match, and there is no error or warning.
• Categories, stores, layouts, related products, filters, downloads. All are join tables. Setting a category means deleting the rows that are there and inserting the ones that should be, and getting the delete wrong un-files a product from everything it was in.
• SEO keywords. They live in oc_seo_url, keyed by a value and a key rather than by product, and they are unique per store and language. A duplicate there does not fail loudly. It takes over a URL that used to point somewhere else.

None of this is exotic. It is more surface than a single UPDATE suggests, and the surface is invisible from the SQL prompt.

Route three: export the backup, edit it, restore it

Do not. It is the route that turns a pricing mistake into an outage.

A restore is not a merge. It replaces the tables in the file with what the file says. Any order, review, customer or stock movement that happened between the export and the restore is inside the window that gets overwritten, and a spreadsheet round-trip through CSV is an excellent way to turn a leading zero into a number and a UTF-8 name into mojibake on the way.

If you have already done this and are reading this afterwards: the damage is in the tables the file contained, for the period between export and restore, and the only fix is the backup you took before restoring.

Route four: an import extension

The right answer for anything recurring, with one thing worth checking before you pick one, because most of them differ on this point:

• Does it tell you what it is going to do before it does it? Not a row count: the changes themselves, per product, old value beside new.
• What does it do with a bad row? A price that is not a number, a category that does not exist, a required column missing. Skipping it silently and reporting success is the behaviour that costs you a fortnight.
• Can you undo it? Once applied, is there anything between you and a database restore?
• Does it write through OpenCart's own model, or straight to SQL? This is the difference between the twenty tables above being handled and being your problem.

Whatever you use: three things first

1. Take a backup, and restore it somewhere once. A backup nobody has ever restored is a hypothesis. Ten minutes now is the cheapest ten minutes in this guide.
2. Count first. Run the SELECT that matches the same rows your UPDATE will hit. If the number surprises you, your filter is wrong, and you have found that out for free.
3. Do it on a copy of the store. Use a copy with your catalogue in it, not a fresh install, because the rows that break a bulk edit are always the strange ones you forgot you had.