复制 MySQL 数据并创建一个有条件的新记录 [英] Copying MySQL data and creating a new record with a condition

问题描述

最初我必须从旧表中获取 ip 并将其放入新表中，其中电子邮件在两者中都匹配.

Originally I had to get the ip from old table and place it into the new table where the emails match in both.

INSERT INTO new (ip)
SELECT old.ip 
FROM old
INNER JOIN new on old.email = new.email;

我现在需要更改此查询以执行以下操作:

I now need to change this query to do the following:

从旧表中获取ip
与新表中该行的电子邮件相匹配
从新表中获取 user_id
使用user_id和ip在组合表中创建记录

Get ip from old table
Match it the email from that row in the new table
Get the user_id from new table
Create a record in combined table with user_id and ip

到目前为止的查询:

这接近了吗?似乎不对:/

Is this close? Doesn't seem right :/

comb_uid = SELECT old.ip FROM old  
WHERE new.email = old.email

comb_ip = SELECT new.user_id FROM new  

INSERT INTO combined (user_id, ip)
VALUES comb_uid, comb_ip 

推荐答案

你已经足够接近了.只需将 new 更改为 combined.

you were close enough. just change new into combined.

INSERT INTO combined (ip, user_ID)
SELECT  old.ip, new.user_ID
FROM    old
        INNER JOIN new 
            on old.email = new.email;

更新 1

INSERT INTO combined (ip, user_ID, status)
SELECT  old.ip, new.user_ID, 'approved'
FROM    old
        INNER JOIN new 
            on old.email = new.email;

这篇关于复制 MySQL 数据并创建一个有条件的新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！