PlacementEdge PlacementEdge - 1 year ago 54
SQL Question

I need to remove all leading 0's in a column

So this is the query I want to use. I think it's easier to just look at it and see where I am in the wrong:

UPDATE * FROM 'catalog_product_entity_varchar'
WHERE 'attribute_id' = '176';

So in my table there are a lot of rows with attribute_id = 176, and the column "value" has a lot of leading 0's. I want to remove all of the leading 0's from value when the attribute_id is 176.

This is a bit beyond me, but it seems faster than trying to update any other way.

Answer Source

Your SQL contains errors. First, remove FROM, it is not using with UPDATE. Second, write correct SET to update column, not just select.

UPDATE `catalog_product_entity_varchar`
SET `value` = TRIM(LEADING '0' FROM `value`)
WHERE `attribute_id` = '176';

This must be working query. If this not works, add table structure and some data example, please.