Skip to content

Instantly share code, notes, and snippets.

@aschweigert

aschweigert/gist:9dd8391d7c47cbe11c80

Last active February 3, 2016 20:52
Show Gist options
  • Save aschweigert/9dd8391d7c47cbe11c80 to your computer and use it in GitHub Desktop.
Save aschweigert/9dd8391d7c47cbe11c80 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. aschweigert revised this gist Feb 3, 2016. 1 changed file with 15 additions and 16 deletions.
    31 changes: 15 additions & 16 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,20 +1,19 @@
    -- wp_14_posts all posts that are post_status = publish and post_type = post
    -- maybe set a date range
    -- put the ids somewhere
    drop temporary table if exists post_ids;
    create temporary table if not exists post_ids
    select ID from wp_14_posts where post_status = 'publish' and post_type = 'post' and post_date < '2016-02-03 00:00:000';

    select * from post_ids left join wp_14_postmeta on post_ids.ID = wp_14_postmeta.post_id
    and wp_14_postmeta.meta_key = 'featured-image-display'

    update wp_14_postmeta a
    right join post_ids b on
    a.post_id = b.ID
    set post_id = ID, meta_key = 'featured-image-display', meta_value = false;

    //then in wp_14_postmeta if a row with this id and this meta key already exists, update it, otherwise make a new row

    //set post_id = this_post_id set meta_key = featured-image-display set meta_value = false
    select ID from wp_14_posts
    where post_status = 'publish'
    and post_type = 'post'
    and post_date < '2016-02-03 00:00:000';
    delete wp_14_postmeta from wp_14_postmeta join post_ids
    on wp_14_postmeta.post_id = post_ids.ID
    and wp_14_postmeta.meta_key = 'featured-image-display';
    insert into wp_14_postmeta (post_id, meta_key, meta_value)
    select ID, 'featured-image-display', false
    from post_ids;

    /* to check your work
    select * from wp_14_postmeta a
    right join post_ids b
    on a.post_id = b.ID
    and a.meta_key = 'featured-image-display';*/

  2. aschweigert revised this gist Feb 3, 2016. No changes.
  3. aschweigert revised this gist Feb 3, 2016. No changes.
  4. aschweigert revised this gist Feb 3, 2016. 1 changed file with 6 additions and 4 deletions.
    10 changes: 6 additions & 4 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -4,12 +4,14 @@
    drop temporary table if exists post_ids;
    create temporary table if not exists post_ids
    select ID from wp_14_posts where post_status = 'publish' and post_type = 'post' and post_date < '2016-02-03 00:00:000';

    select * from wp_14_postmeta left join post_ids on post_ids.ID = wp_14_postmeta.post_id
    and meta_key = 'featured-image-display';

    select * from wp_14_postmeta left join post_ids on post_ids.ID = wp_14_postmeta.post_id
    select * from post_ids left join wp_14_postmeta on post_ids.ID = wp_14_postmeta.post_id
    and wp_14_postmeta.meta_key = 'featured-image-display'

    update wp_14_postmeta a
    right join post_ids b on
    a.post_id = b.ID
    set post_id = ID, meta_key = 'featured-image-display', meta_value = false;

    //then in wp_14_postmeta if a row with this id and this meta key already exists, update it, otherwise make a new row

  5. aschweigert created this gist Feb 3, 2016.
    18 changes: 18 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    -- wp_14_posts all posts that are post_status = publish and post_type = post
    -- maybe set a date range
    -- put the ids somewhere
    drop temporary table if exists post_ids;
    create temporary table if not exists post_ids
    select ID from wp_14_posts where post_status = 'publish' and post_type = 'post' and post_date < '2016-02-03 00:00:000';

    select * from wp_14_postmeta left join post_ids on post_ids.ID = wp_14_postmeta.post_id
    and meta_key = 'featured-image-display';

    select * from wp_14_postmeta left join post_ids on post_ids.ID = wp_14_postmeta.post_id
    and wp_14_postmeta.meta_key = 'featured-image-display'

    //then in wp_14_postmeta if a row with this id and this meta key already exists, update it, otherwise make a new row

    //set post_id = this_post_id set meta_key = featured-image-display set meta_value = false