Change a value (just a flag 0/1) depending on an entry in another table and - important - leave everything else unchanged

[nishika]

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 https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=a83a52f0080f532ee84fd4d4e5b4e931

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

[Jolied]

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