資料表匯入資料表？不重複？sql指令

mysql

火爆浪子 2016-12-30 19:04:14 ‧ 4900 瀏覽

INSERT INTO customer(email,address,name,phone,phone2)  
SELECT email,address,name,phone,phone2  
FROM buy  
WHERE NOT EXISTS(  
      SELECT *  
      FROM customer  
      WHERE phone = phone and (phone2 = phone or phone = phone2)
);

我想把buy的資料表中的筆數選擇email,address,name,phone,phone2 進入customer
但是若在進入customer中phone或phone2已經存在，則略過
這樣好像錯了

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

做工仔人!

iT邦大師 1 級 ‧ 2016-12-30 20:52:43

最佳解答

INSERT INTO customer(email,address,name,phone,phone2)  
SELECT email,address,name,phone,phone2  
FROM buy A LEFT JOIN customer B on A.email=B.email AND A.address =B.address  
                    AND (A.phon = B.phone OR A.phone=B.phone2 OR   
                         A.phon2 = B.phone OR A.phone2=B.phone2)  
WHERE B.email IS NULL ;