Skip to content

Instantly share code, notes, and snippets.

@aprea

aprea/ms-extract-umeta-clean.sql

Forked from webaware/ms-extract-umeta-clean.sql
Created August 15, 2014 01:41
Show Gist options
  • Save aprea/484539cfea18e253bc2a to your computer and use it in GitHub Desktop.
Save aprea/484539cfea18e253bc2a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @webaware webaware created this gist Aug 14, 2014.
    22 changes: 22 additions & 0 deletions ms-extract-umeta-clean.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,22 @@
    -- delete any usermeta specific to the other subsites
    delete from wp_usermeta where meta_key regexp '^wp_([0-9]+)_';

    -- duplicate the wp_usermeta structure in a working data table,
    -- but add a unique index for filtering out duplicates
    create table _fix_usermeta like wp_usermeta;
    alter table _fix_usermeta add unique(user_id, meta_key);

    -- copy the site-specific usermeta, keeping only the last of each duplicate
    insert into _fix_usermeta
    select * from wp_usermeta
    where meta_key like 'wp\_%'
    order by user_id, meta_key, umeta_id
    on duplicate key update umeta_id=values(umeta_id), meta_value=values(meta_value);

    -- remove the first of each duplicate
    delete from wp_usermeta
    where meta_key like 'wp\_%'
    and not exists (select * from _fix_usermeta where umeta_id = wp_usermeta.umeta_id);

    -- remove that working data table
    drop table _fix_usermeta;