回答編集履歴
5
変更
answer
CHANGED
|
@@ -1,6 +1,13 @@
|
|
|
1
|
-
NOT EXIST版です。
|
|
1
|
+
NOT EXIST版です。訂正です。
|
|
2
2
|
|
|
3
3
|
```sql
|
|
4
|
+
INSERT INTO RS (code,name) SELECT code,name FROM `ALL`
|
|
5
|
+
WHERE NOT EXISTS (SELECT 1 FROM RS WHERE code=`ALL`.code);
|
|
6
|
+
```
|
|
7
|
+
|
|
8
|
+
NOT IN 版です。
|
|
9
|
+
|
|
10
|
+
```sql
|
|
4
11
|
INSERT INTO RS(code,name) SELECT code,name FROM `ALL` WHERE `ALL`.code NOT IN (SELECT code FROM RS);
|
|
5
12
|
```
|
|
6
13
|
|
4
変更
answer
CHANGED
|
@@ -11,7 +11,7 @@
|
|
|
11
11
|
|
|
12
12
|
> ALLが最新情報のため、RSを最新情報に更新したいと思います
|
|
13
13
|
|
|
14
|
-
nameが変更される場合は
|
|
14
|
+
nameが変更される場合は、但し（codeがprimary key）
|
|
15
15
|
```
|
|
16
16
|
INSERT INTO RS (code,name) SELECT t.code , t.name FROM `ALL` AS t
|
|
17
17
|
ON DUPLICATE KEY UPDATE RS.name = t.name
|
3
変更
answer
CHANGED
|
@@ -7,4 +7,12 @@
|
|
|
7
7
|
JOIN でも可能
|
|
8
8
|
```sql
|
|
9
9
|
INSERT INTO RS(code,name) SELECT t1.code,t1.name FROM `ALL` t1 LEFT JOIN RS t2 USING(code) WHERE t2.code IS NULL;
|
|
10
|
+
```
|
|
11
|
+
|
|
12
|
+
> ALLが最新情報のため、RSを最新情報に更新したいと思います
|
|
13
|
+
|
|
14
|
+
nameが変更される場合は
|
|
15
|
+
```
|
|
16
|
+
INSERT INTO RS (code,name) SELECT t.code , t.name FROM `ALL` AS t
|
|
17
|
+
ON DUPLICATE KEY UPDATE RS.name = t.name
|
|
10
18
|
```
|
2
変更
answer
CHANGED
|
@@ -1,10 +1,10 @@
|
|
|
1
1
|
NOT EXIST版です。
|
|
2
2
|
|
|
3
3
|
```sql
|
|
4
|
-
INSERT INTO RS(code,name) SELECT code,name FROM ALL WHERE ALL.code NOT IN (SELECT code FROM RS);
|
|
4
|
+
INSERT INTO RS(code,name) SELECT code,name FROM `ALL` WHERE `ALL`.code NOT IN (SELECT code FROM RS);
|
|
5
5
|
```
|
|
6
6
|
|
|
7
7
|
JOIN でも可能
|
|
8
8
|
```sql
|
|
9
|
-
INSERT INTO RS(code,name) SELECT t1.code,t1.name FROM ALL t1 LEFT JOIN RS t2 USING(code) WHERE t2.code IS NULL;
|
|
9
|
+
INSERT INTO RS(code,name) SELECT t1.code,t1.name FROM `ALL` t1 LEFT JOIN RS t2 USING(code) WHERE t2.code IS NULL;
|
|
10
10
|
```
|
1
追加
answer
CHANGED
|
@@ -2,4 +2,9 @@
|
|
|
2
2
|
|
|
3
3
|
```sql
|
|
4
4
|
INSERT INTO RS(code,name) SELECT code,name FROM ALL WHERE ALL.code NOT IN (SELECT code FROM RS);
|
|
5
|
+
```
|
|
6
|
+
|
|
7
|
+
JOIN でも可能
|
|
8
|
+
```sql
|
|
9
|
+
INSERT INTO RS(code,name) SELECT t1.code,t1.name FROM ALL t1 LEFT JOIN RS t2 USING(code) WHERE t2.code IS NULL;
|
|
5
10
|
```
|