我有两个类似的表table_a和table_btable_a是我当前的数据，我需要使用 table_b 更新此信息，这是一个临时表。 两者之间的唯一区别是table_a有一个table_b不会有的密码字段。
我正在尝试做几件事。
1.Compare the data based on the "user_id" field.
2. If there is a user_id not found in table_b but there is one in table_a remove that row in table_a
3. If there is a user_id in table_b that is not found in table_a add that row of data to table_a
4. If the user_id is found in both then check two of the fields "email" and "job_code" and make sure they are both whatever table_b says.
我应该按照上面编号的顺序在单独的MySQL语句中执行此操作吗？ 以下是我对上述陈述的尝试。任何帮助或故障排除将不胜感激。谢谢！
声明 1：
SELECT * FROM table_a
WHERE table_a.user_id
NOT IN (
SELECT table_b.user_id
FROM table_b
WHERE table_a.user_id=table_b.user_id
) // But how do I delete those records that are selected?
声明 2：
SELECT * FROM table_b
WHERE table_b.user_id
NOT IN (
SELECT table_a.user_id
FROM table_a
WHERE table_a.user_id=table_b.user_id
) //How do I Insert these records that I have in table_b but not in table_a
报表3：
SELECT email FROM table_b
WHERE table_b.user_id = table_a.user_id,
AND table_b.email != table_a.email //now I need to update table_a with the emails from table_b
语句 #1
DELETE A.*
FROM table_a A
LEFT JOIN table_b B
USING (user_id)
WHERE B.user_id IS NULL;
声明 #2
INSERT INTO table_b (column1,column2,...,columnN)
SELECT A.column1,A.column2,...,A.columnN
FROM table_b B LEFT JOIN table_a A
USING (user_id)
WHERE A.user_id IS NULL;
声明 #3
UPDATE table_a A INNER JOIN table_b B USING (user_id)
SET A.email = B.email,A.job_code = B.job_code;
更新 2012-06-19 11：54 EDT
A 和 B 只是表的别名。
例如，语句 #3 的查询可以这样编写：
UPDATE table_a as A INNER JOIN table_b as B USING (user_id)
SET A.email = B.email,A.job_code = B.job_code;
或者没有任何别名，如下所示：
UPDATE table_a INNER JOIN table_b USING (user_id)
SET table_a.email = table_b.email,table_a.job_code = table_b.job_code;
第一个问题的解决方案 ：：1）
delete FROM table_a
left join table_b
on
table_a.user_id=table_b.user_id
and table_a.user_id is null