When extra information is required to be stored for a specific record in ‘wp_posts’, it can be stored in the ‘wp_postmeta’ table. One such bit of information is when an attached image is to be used as the site-icon. I realised this when I added an icon (ie a favicon) to a local version of my site only to not see it not show up when I uploaded the files to the online version. I had updated the wp_post table but this also did not rectify the situation. The wp_postmeta table specifies that the image is to be used as the site icon.

I wasn’t sure how well the online data would match with my local version so I did some checks which I describe here for reference:

1. wp_postmeta was exported to local drive and a temporary copy was made (called wp_postmetab).

2. Next I did some exploration. I did a union of both tables and grouped them. In this case, most records matched with the exception of ‘_edit_lock’ field. This made sense as it would change when a post was opened and updated. In this case, I was able to delete the ‘_edit_lock’ records in 1 table and use those from the other table.
Using the query shown below, I looked for ‘counts’ of 1 which would indicate either unique entries or non-matching entries. I was ignoring ‘meta_id’ as it is not used/recorded elsewhere as far as I have been able to determine (this site details that: wp-staging.com/docs/the-wordpress-database-structure/#wp_posts).
Cases where a unique value existed in either table would just be added to the merged table. If the information is needed, it will be there and if not, no big deal that it is there.

For clarity, the query below produces a union of two tables with extra columns to show when records are grouped and which database/table the record(s) come from.
[code lang=”sql”]
SELECT Count(*) as cnt, SUM(DB1) as Sum1, SUM(DB2) as Sum2, UN.* FROM
(
SELECT 1 as DB1, 0 as DB2, pm.* FROM DB_Name.wp_postmeta as pm
UNION ALL
SELECT 0 as DB1, 1 as DB2, pmb.* FROM DB_Name.wp_postmetab as pmb
) UN
GROUP BY post_id, meta_key, meta_value;
[/code]

3. For those cases where post_id and meta_key matched but meta_value did not, a decision had to be made for each record. I created a copy of the combined tables without the ‘meta_id’ column (this column was added after). Using ‘UNION’ rather than ‘UNION ALL’ keeps distinct values only. I compared against the previous temporary table for those records that needed to be deleted or amended.
[code lang=”sql”]
CREATE TABLE TempPostMeta
SELECT post_id, meta_key, meta_value FROM DB_Name.wp_postmeta as pm
UNION
SELECT post_id, meta_key, meta_value FROM DB_Name.wp_postmetab as pmb;

ALTER TABLE TempPostMeta
ADD meta_id bigint(20) unsigned AUTO_INCREMENT PRIMARY KEY FIRST;
[/code]

4. Once TempPostMeta table was considered a suitable replacement I truncated the data in the wp_postmeta and replaced with the data from TempPostMeta table.
[code lang=”sql”]
TRUNCATE TABLE DB_Name.wp_postmeta;

INSERT INTO DB_Name.wp_postmeta(meta_id, post_id, meta_key, meta_value)
SELECT meta_id, post_id, meta_key, meta_value from DB_Name.TempPostMeta;
[/code]

5. I then did the same steps to add the data to the online version and tested which found everything to be working as it should be.