回答編集履歴
2
調整
test
CHANGED
|
@@ -65,30 +65,6 @@
|
|
|
65
65
|
gender=values(gender)
|
|
66
66
|
;
|
|
67
67
|
COMMIT;
|
|
68
|
+
```
|
|
69
|
+
(ダブりを調整)
|
|
68
70
|
|
|
69
|
-
|
|
70
|
-
BEGIN;
|
|
71
|
-
INSERT INTO `users` (`email`, `password`)
|
|
72
|
-
VALUES ('a1', 'b1'),('a2', 'b2'),('a3', 'b3');
|
|
73
|
-
INSERT INTO `profile`(`uid`,`nickname`,`family_name`,`given_name`,`gender`)
|
|
74
|
-
SELECT * FROM (
|
|
75
|
-
SELECT t1.uid,
|
|
76
|
-
COALESCE(ELT(`num`,'c1','c2','c3'),`nickname`) as `nickname`,
|
|
77
|
-
COALESCE(ELT(`num`,'d1','d2','d3'),`family_name`) as `family_name`,
|
|
78
|
-
COALESCE(ELT(`num`,'e1','e2','e3'),`given_name`) as `given_name`,
|
|
79
|
-
COALESCE(ELT(`num`,'1','2','1'),`gender`) as `gender`
|
|
80
|
-
FROM `profile` as t1
|
|
81
|
-
INNER JOIN (
|
|
82
|
-
SELECT `uid`,FIELD(`email`,'a1','a2','a3') as `num`
|
|
83
|
-
FROM `users`
|
|
84
|
-
) as t2
|
|
85
|
-
ON t1.uid = t2.uid
|
|
86
|
-
) sub
|
|
87
|
-
ON DUPLICATE KEY UPDATE
|
|
88
|
-
nickname=sub.nickname,
|
|
89
|
-
family_name=sub.family_name,
|
|
90
|
-
given_name=sub.given_name,
|
|
91
|
-
gender=sub.gender;
|
|
92
|
-
COMMIT;
|
|
93
|
-
```
|
|
94
|
-
|
1
chousei
test
CHANGED
|
@@ -40,3 +40,55 @@
|
|
|
40
40
|
ただし情報が不足しているのでprofileの殆どの値はnullです
|
|
41
41
|
※命題ではprofileをnot null指定しているのが意味不明。もしそうならDEFAULT値を設定する必要があります
|
|
42
42
|
|
|
43
|
+
# 追記
|
|
44
|
+
usersテーブルに入れるタイミングでprofileも更新
|
|
45
|
+
|
|
46
|
+
```SQL
|
|
47
|
+
BEGIN;
|
|
48
|
+
INSERT INTO `users` (email, password) VALUES ('a1', 'b1'),('a2', 'b2'),('a3', 'b3');
|
|
49
|
+
insert into profile(uid,nickname,family_name,given_name,gender)
|
|
50
|
+
select * from(select t1.uid,
|
|
51
|
+
COALESCE(ELT(num,'c1','c2','c3'),nickname),
|
|
52
|
+
COALESCE(ELT(num,'d1','d2','d3'),family_name),
|
|
53
|
+
COALESCE(ELT(num,'e1','e2','e3'),given_name),
|
|
54
|
+
COALESCE(ELT(num,'1','2','1'),gender)
|
|
55
|
+
from profile as t1
|
|
56
|
+
inner join (
|
|
57
|
+
select uid,FIELD(email,'a1','a2','a3') as num
|
|
58
|
+
from users
|
|
59
|
+
) as t2
|
|
60
|
+
on t1.uid = t2.uid) sub
|
|
61
|
+
on duplicate key update
|
|
62
|
+
nickname=values(nickname),
|
|
63
|
+
family_name=values(family_name),
|
|
64
|
+
given_name=values(given_name),
|
|
65
|
+
gender=values(gender)
|
|
66
|
+
;
|
|
67
|
+
COMMIT;
|
|
68
|
+
|
|
69
|
+
|
|
70
|
+
BEGIN;
|
|
71
|
+
INSERT INTO `users` (`email`, `password`)
|
|
72
|
+
VALUES ('a1', 'b1'),('a2', 'b2'),('a3', 'b3');
|
|
73
|
+
INSERT INTO `profile`(`uid`,`nickname`,`family_name`,`given_name`,`gender`)
|
|
74
|
+
SELECT * FROM (
|
|
75
|
+
SELECT t1.uid,
|
|
76
|
+
COALESCE(ELT(`num`,'c1','c2','c3'),`nickname`) as `nickname`,
|
|
77
|
+
COALESCE(ELT(`num`,'d1','d2','d3'),`family_name`) as `family_name`,
|
|
78
|
+
COALESCE(ELT(`num`,'e1','e2','e3'),`given_name`) as `given_name`,
|
|
79
|
+
COALESCE(ELT(`num`,'1','2','1'),`gender`) as `gender`
|
|
80
|
+
FROM `profile` as t1
|
|
81
|
+
INNER JOIN (
|
|
82
|
+
SELECT `uid`,FIELD(`email`,'a1','a2','a3') as `num`
|
|
83
|
+
FROM `users`
|
|
84
|
+
) as t2
|
|
85
|
+
ON t1.uid = t2.uid
|
|
86
|
+
) sub
|
|
87
|
+
ON DUPLICATE KEY UPDATE
|
|
88
|
+
nickname=sub.nickname,
|
|
89
|
+
family_name=sub.family_name,
|
|
90
|
+
given_name=sub.given_name,
|
|
91
|
+
gender=sub.gender;
|
|
92
|
+
COMMIT;
|
|
93
|
+
```
|
|
94
|
+
|