首先感謝大家的回復。
表A如下
id class
a b
a b1
c d
e f
=====
表B如下
id class check
a b 1
a b1 0
=====
現在，就可以把A表中的
c d
e f
放入到表b中去。
感謝您的回復。
uj5u.com熱心網友回復：
INSERT INTO B
SELECT * ,
0
FROM A
WHERE NOT EXISTS ( SELECT *
FROM B
WHERE A.id = B.id
AND B.class = A.class );
SELECT * FROM B
uj5u.com熱心網友回復：
謝謝。我希望B表結果如下：
表B如下
id class check
a b 1
a b1 0
c d
e f
uj5u.com熱心網友回復：
表B如下id class check
a b 1
a b1 0
c d 0
e f 0
check欄位，在剛從A表拿來插入時候，初值是0，然后在其他條件下，會被更新成1
uj5u.com熱心網友回復：
首先，看表A，也就是 view_aaa 的資料，如下SELECT uid, class2 FROM view_aaa ; 這句話，執行正常
uid class2
4 134
4 135
5 135
6 135
===============
然后，看表B，也就是 shizi_certificate_member （師資授權）的前兩列的資料，來自于表A的uid, class2，最開始為空.
以下陳述句指行正常：
SELECT view_aaa.uid, view_aaa.class2 FROM view_aaa, shizi_certificate_member
WHERE view_aaa.uid=shizi_certificate_member.uid
and view_aaa.class2 = shizi_certificate_member.columnid
==============================
uid class2
結果為空，因為 shizi_certificate_member 本身為空。
最后，上面兩個sql陳述句結合起來看：
SELECT uid, class2 FROM view_aaa
WHERE NOT EXISTS (SELECT view_aaa.uid, view_aaa.class2 FROM view_aaa, shizi_certificate_member
WHERE view_aaa.uid=shizi_certificate_member.uid
and view_aaa.class2 = shizi_certificate_member.columnid);
執行結果如下：
[SQL]#SELECT * FROM view_aaa
#SELECT uid, class2, 0 FROM view_aaa;
SELECT uid, class2 FROM view_aaa
WHERE NOT EXISTS (SELECT view_aaa.uid, view_aaa.class2 FROM view_aaa, shizi_certificate_member
WHERE view_aaa.uid=shizi_certificate_member.uid
and view_aaa.class2 = shizi_certificate_member.columnid);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT view_aaa.uid, view_aaa.class2 FROM view_aaa, shizi_certificate_member
' at line 5
我現在還沒有明白，為何出錯了。
還請大家來幫幫我吧，非常感謝大家
uj5u.com熱心網友回復：
MYSQL版本是什么？是否支持 NOT EXISTSuj5u.com熱心網友回復：
not exists 可以執行的，不存在不支持的問題。uj5u.com熱心網友回復：
我處理了一下，腳本如下。insert into shizi_certificate_member (uid, columnid)
SELECT view_aaa.uid, class2 FROM view_aaa
WHERE NOT EXISTS(
select view_aaa.uid, view_aaa.class2 from shizi_certificate_member, view_aaa
WHERE view_aaa.uid = shizi_certificate_member.uid
and view_aaa.class2 = shizi_certificate_member.columnid )
請批評指點，看看有沒有錯誤。
感謝您的回復。
uj5u.com熱心網友回復：
insert into shizi_certificate_member (uid, columnid)
SELECT view_aaa.uid, class2 FROM view_aaa
WHERE NOT EXISTS(
select 1 from shizi_certificate_member, view_aaa
WHERE view_aaa.uid = shizi_certificate_member.uid
and view_aaa.class2 = shizi_certificate_member.columnid )
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/115311.html
標籤：MySQL
下一篇：mysql 鎖問題