0

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' 
SET TRIM(LEADING '0' FROM 'value') 
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.

Andrew
  • 1,858
  • 13
  • 15
PlacementEdge
  • 33
  • 1
  • 6
  • what is wrong with that query then ? – Ren Mark Almojera Cerdan May 17 '16 at 03:44
  • 1
    what db are you using – Hogan May 17 '16 at 04:23
  • 1
    What database are you using? There seems to be a similar question [here](http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement). Have you taken a look at it? – Ravindra HV May 17 '16 at 04:23
  • I'm using MariaDB (mysql). I tried running the above query and it keeps failing, saying I have an error in my format. – PlacementEdge May 17 '16 at 04:29
  • @PlacementEdge that is because you do... don't have FROM in an update statement typically. – Hogan May 17 '16 at 04:30
  • Single quotes are for string literals. Use double quotes instead. (Or better don't use quotes at all, they are not needed with the names given.) I don't know if MariaDB handles this differently from the SQL standard, but as far as I see it, you are comparing two strings, 'attribute_id' and '176', here, so the WHERE clause will never be true. – Thorsten Kettner May 17 '16 at 05:24

1 Answers1

5

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.

Andrew
  • 1,858
  • 13
  • 15