回答編集履歴
2
調整
test
CHANGED
@@ -23,3 +23,11 @@
|
|
23
23
|
delete from user where SCORE<=50;
|
24
24
|
commit;
|
25
25
|
```
|
26
|
+
# 調整
|
27
|
+
deleteで「SCORE<=50」を条件に使いたくない場合
|
28
|
+
```SQL
|
29
|
+
start transaction;
|
30
|
+
update hoge,user set hoge.status='update',USER.SCORE=null where user.SCORE<=50 and user.ID=hoge.user_id;
|
31
|
+
delete from user where SCORE is null;
|
32
|
+
commit;
|
33
|
+
```
|
1
調整
test
CHANGED
@@ -1,2 +1,25 @@
|
|
1
1
|
hogeテーブルのuser_idに該当するレコードをuserテーブルから削除してしまうのは
|
2
2
|
リレーションDBとして合理的ではありません
|
3
|
+
|
4
|
+
```SQL
|
5
|
+
create table user(ID int primary key,USER varchar(20),SCORE int);
|
6
|
+
insert into user values
|
7
|
+
(1,'TARO',90),
|
8
|
+
(2,'JIRO',40),
|
9
|
+
(3,'SABU',80),
|
10
|
+
(4,'SIRO',30),
|
11
|
+
(5,'GORO',10);
|
12
|
+
|
13
|
+
create table hoge(USER_ID int primary key, status varchar(10));
|
14
|
+
insert into hoge values
|
15
|
+
(1,'通過'),
|
16
|
+
(2,'通過'),
|
17
|
+
(3,'通過'),
|
18
|
+
(4,'通過'),
|
19
|
+
(5,'通過');
|
20
|
+
|
21
|
+
start transaction;
|
22
|
+
update hoge,user set hoge.status='update' where user.SCORE<=50 and user.ID=hoge.user_id;
|
23
|
+
delete from user where SCORE<=50;
|
24
|
+
commit;
|
25
|
+
```
|