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



  • 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



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2