0

I have come unstuck with a certain SQL query on a specific data set. Below is an example of the meta_value stored in the database:

a:1:{i:0;a:2:{s:11:"ingredients";a:7:{i:0;a:5:{s:6:"amount";s:1:"3";s:4:"unit";s:3:"cup";s:4:"name";s:23:"spring mix salad greens";s:5:"notes";s:0:"";s:2:"id";i:7263;}i:1;a:5:{s:6:"amount";s:1:"3";s:4:"unit";s:4:"unit";s:4:"name";s:28:"to 4 radishes, thinly sliced";s:5:"notes";s:0:"";s:2:"id";i:7264;}i:2;a:5:{s:6:"amount";s:1:"1";s:4:"unit";s:4:"unit";s:4:"name";s:60:"small orange or tangerine, peeled, seeded, and thinly sliced";s:5:"notes";s:0:"";s:2:"id";i:7265;}i:3;a:5:{s:6:"amount";s:5:"1⁄2";s:4:"unit";s:4:"unit";s:4:"name";s:46:"cucumber, scrubbed or peeled and thinly sliced";s:5:"notes";s:0:"";s:2:"id";i:7266;}i:4;a:5:{s:6:"amount";s:1:"1";s:4:"unit";s:10:"tablespoon";s:4:"name";s:9:"olive oil";s:5:"notes";s:0:"";s:2:"id";i:6785;}i:5;a:5:{s:6:"amount";s:1:"2";s:4:"unit";s:8:"teaspoon";s:4:"name";s:17:"fresh lemon juice";s:5:"notes";s:0:"";s:2:"id";i:6935;}i:6;a:5:{s:6:"amount";s:5:"1⁄2";s:4:"unit";s:8:"teaspoon";s:4:"name";s:4:"salt";s:5:"notes";s:0:"";s:2:"id";i:7215;}}s:4:"name";s:0:"";}}

I wish to replace the following portion:

s:4:"unit";s:4:"unit";

with

s:4:"unit";s:0:"";

However all my attempts completely change the entire value not just and instances of this value.

Any ideas?

I've tried the following but get the error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''s:4:"unit")' at line 4

UPDATE
    `wp_postmeta`
SET
    `wprm_ingredients` =
REPLACE
    (
        'wprm_ingredients',
        's:4:"unit";s:4:"unit";',
        's:4:"unit";s:0:"";'
    )
Daniel Vickers
  • 1,054
  • 1
  • 12
  • 32
  • This looks a lot like storing large json objects in column data. If you want to treat json this way in your database, you're in a for a world of hurt. Much better to make a choice: use a nosql database option that handles json objects natively, or map out the properties in your json objects into a real relational schema. This middle-ground you're trying to walk is.. unpleasant. – Joel Coehoorn Nov 16 '20 at 17:28
  • The query works on shown sample data. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c653e41e6864c772d66176e25f922785 – Akina Nov 16 '20 at 17:28
  • 1
    @JoelCoehoorn, It's not JSON, it's a serialized PHP object. There is no support for modifying serialized PHP data in SQL. The best way to handle this is to fetch the object into a PHP app, deserialize it, change it using PHP, then re-serialize it in the database. Repeat for each row. – Bill Karwin Nov 16 '20 at 18:07
  • @BillKarwin that sounds like fun... – Daniel Vickers Nov 16 '20 at 18:38
  • Also use the correct quotes. You put `'wprm_ingredients'` in single-quotes, which are for string literals, not column identifiers. See https://stackoverflow.com/q/11321491/20860 – Bill Karwin Nov 16 '20 at 19:05
  • @BillKarwin I see. That doesn't change the meat of my comment, though: if you want serialized objects in your DB, then use a DB optimized for those objects. Otherwise build a real schema and use the DB as it was intended. – Joel Coehoorn Nov 16 '20 at 19:54
  • @JoelCoehoorn its just the way advanced custom fields a widely used wordpress plugin stores the data unfortunately. – Daniel Vickers Nov 16 '20 at 20:30
  • @JoelCoehoorn, I agree, trying to use SQL to do surgery on fields within a serialized object is bound to be ugly. This appears to be a WordPress question, so switching to another type of database is not possible (WordPress only supports MySQL). – Bill Karwin Nov 16 '20 at 22:32

0 Answers0