2

I'm trying to replace a taxonomy entry with a flag. The fields are kept in different tables. Here's what I did so far on db<>fiddle:

CREATE TABLE `job_postmeta` (
  `meta_id` bigint(20) UNSIGNED NOT NULL,
  `post_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL
);

CREATE TABLE `job_term_relationships` (
  `object_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `term_taxonomy_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `term_order` int(11) NOT NULL DEFAULT 0
);


INSERT INTO `job_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (1, 1, '_remote_job', '0')
INSERT INTO `job_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (2, 2, '_remote_job', '0')
INSERT INTO `job_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (3, 3, '_remote_job', '0')
INSERT INTO `job_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (2, 3, '_something_else', 'ABCD')
INSERT INTO `job_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES (4, 4, '_remote_job', '0')
INSERT INTO `job_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (2, 263, 0)
INSERT INTO `job_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (4, 263, 0)
INSERT INTO `job_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (1, 250, 0)
INSERT INTO `job_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (1, 263, 0)
INSERT INTO `job_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (2, 250, 0)
SELECT * FROM job_postmeta;
meta_id | post_id | meta_key        | meta_value
------: | ------: | :-------------- | :---------
      1 |       1 | _remote_job     | 0         
      2 |       2 | _remote_job     | 0         
      3 |       3 | _remote_job     | 0         
      2 |       3 | _something_else | ABCD      
      4 |       4 | _remote_job     | 0         
SELECT * FROM  job_term_relationships;
object_id | term_taxonomy_id | term_order
--------: | ---------------: | ---------:
        2 |              263 |          0
        4 |              263 |          0
        1 |              250 |          0
        1 |              263 |          0
        2 |              250 |          0
UPDATE job_postmeta

SET meta_value = 
(
  SELECT '1' 
  FROM job_term_relationships
  WHERE term_taxonomy_id = 263
  AND object_id = meta_id

  )
WHERE meta_key = '_remote_job' 
SELECT * FROM job_postmeta;
meta_id | post_id | meta_key        | meta_value
------: | ------: | :-------------- | :---------
      1 |       1 | _remote_job     | 1         
      2 |       2 | _remote_job     | 1         
      3 |       3 | _remote_job     | null      
      2 |       3 | _something_else | ABCD      
      4 |       4 | _remote_job     | 1         

db<>fiddle here

The only thing I didn't achive is keeping the '0' in _remote_job if there's no entry of '263' for the respectiv row. My first attempt set also _something_else to NULL. My attempts with a simple AND condition for meta_value = 0 or CASE to SET meta_value SELECT '0' failed.

Any guidance would be appreciated

1 Answer 1

1

You can write proper INNER JOIN UPDATE clause, like :

UPDATE job_postmeta jp
INNER JOIN job_term_relationships jtr on jp.meta_id=jtr.object_id 
SET meta_value = '1' 
WHERE meta_key = '_remote_job'
and term_taxonomy_id = 263;

Result:

meta_id post_id meta_key       meta_value
1          1    _remote_job      1
2          2    _remote_job      1
3          3    _remote_job      0
2          3    _something_else  ABCD
4          4    _remote_job      1

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=4fc653b634229ce6a9d1c3bf51e09220

1
  • Thanks Ergest, as much as I can follow this on db<>fiddle, it doesn't work (for what ever reason) IRL. Instead of 529 entries, that should be found it gives "zero entries affected" 😔
    – stk
    Commented May 7, 2022 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.