Insert value from B table to A table which not duplicate in mysql-CodePudding

I have 2 tables A, B

Table A

user_login  user_email          display_name
--------------------------------------------------
btv1        btv1@chodoixe.com   Bien tap vien 1
btv2        btv2@chodoixe.com   Bien tap vien 2

Table B

meta_id post_id meta_key         meta_value
-------------------------------------------
83758   18181   user-id          108538      <---- insert to user_login, user_email
83759   18181   user-fullname    nguyen dang <---- insert to display_name
83760   18181   user-phone       329420797
83761   18181   car-firm         vinfast
83762   18181   car-model        lux-a20
88974   18782   user-id          24561      <---- insert to user_login, user_email
88975   18782   user-fullname    a hoc      <---- insert to display_name
88976   18782   user-phone       943902211
88977   18782   car-firm         kia
88978   18782   car-model        morning

I want to insert meta_value of user-id, user-fullname of post_id='18181','18782' of table B to user_login, user_email, display_name of table A

My below query return over 100,000 inserted rows. But there are about 1,300 rows in users table have to insert postmeta table

INSERT INTO users(
    user_login,
    user_email,
    display_name
)
select a.meta_value, b.meta_value, c.meta_value from
    (
    SELECT
        meta_value
    FROM
        `postmeta`
    WHERE
        `meta_key` = 'user-id' AND post_id < 5500
) a,
 (
    SELECT
        meta_value
    FROM
        `postmeta`
    WHERE
        `meta_key` = 'user-id' AND post_id < 5500
) b,
(
    SELECT
        meta_value
    FROM
        `postmeta`
    WHERE
        `meta_key` = 'user-fullname' AND post_id < 5500
) c
where not exists (select d.user_login, d.user_email, d.display_name from users d where d.user_login=a.meta_value)

Please give me the true query

Thank you so much !!!

CodePudding user response：

You could simply join Table B over itself on same post_id and meta_key equal to user-fullname then filter selected where right joined table's meta_key is equal to user-id

INSERT INTO `Table A` (`user_login`, `user_email`, `display_name`)
SELECT
    `UserIds`.`meta_value` AS `user_login`,
    `UserIds`.`meta_value` AS `user_email`,
    `UserFullNames`.`meta_value` AS `display_name`
FROM `Table B` AS `UserIds`
INNER JOIN `Table B` AS `UserFullNames` 
    ON `UserFullNames`.`post_id` = `UserIds`.`post_id`
    AND `UserFullNames`.`meta_key` = 'user-fullname'
WHERE `UserIds`.`meta_key` = 'user-id'

CodePudding user response：

Can anyone help me please

Thank you so much !!!