case文とwhere文の併用

Question

したいこと
特定の値が出た場合、そのid番のレコードを削除
ない場合については、idが最大のレコードを削除
phpで書いていたコードについては以下になります。

$sql = 'DELETE FROM cp_plan WHERE id = (CASE WHEN id= '.$id_number;
$sql .=  ' THEN ' .$id_number;
$sql .= ' ELSE (SELECT mid FROM (SELECT max(id) as mid from cp_plan) AS tmp ) END)';

またsqlの表示としては以下になります。

DELETE FROM cp_plan WHERE id = (CASE WHEN id= 176 THEN 176 ELSE (SELECT mid FROM (SELECT max(id) as mid from cp_plan) AS tmp ) END)

これを行ったとき、特定のid番のレコードと最大のレコードの２つが消えてしまいます。
どう直していいのかが特定できないのが現状です。。。
ご助力お願いします。
DBの種類はMySQL、versionは10.1.31、環境はphpmyadminです。

Comments

[m.ts10806]

DBの種類とバージョンを環境情報として記載してください。

[naonao11]

ありがとうございます！ついきさせていただきました！

[m.ts10806]

細かいですがversion10なら正確にはMariaDBですね。実質の後継なので問題はないですが。

Answer 1

SELECT max(id) as mid from cp_plan 

で2件データが存在していたからでは？
実際のデータがどうなっているのか CREATE TABLE, INSERTで提示できると適切なコメントが付き易いかと。

同じSQLでも方言が大きいですから、どのデータベースを使うのかをタグで明示した方が良いです。SQLの観点から Oracle Database, PostgreSQL, MySQL の特徴を整理しよう！

Comments

[naonao11]

なるほど。。ありがとうございます！確認したところ２つとられていました。。。

Answer 2

例えばこんな感じ

• 元データ

create table tbl(id int unique);
insert into tbl values(1),(2),(3),(5),(10);

• id=5を削除

delete from tbl where id=
(select id from (select coalesce(t1.id,(select max(id) from tbl)) as id from tbl as t1
right join (select 5 as id) as t2 using(id)
) as t3)

• id=6を削除しようとして、レコードが無いのでid=10を削除

delete from tbl where id=
(select id from (select coalesce(t1.id,(select max(id) from tbl)) as id from tbl as t1
right join (select 6 as id) as t2 using(id)
) as t3)

Comments

[naonao11]

うまくいきました、ありがとうございます！ちゃんと理解して使えるようにします！

[naonao11]

申し訳ございません。。。
Table 'cp_plan' is specified twice, both as a target for 'DELETE' and as a separate source for data
と出てきました。。。

[yambejp]

ああ、idはuniqueではないんでしたっけ・・・
delete from tbl where id=
(select distinct id from (select coalesce(t1.id,(select max(id) from tbl)) as id from tbl as t1
right join (select 5 as id) as t2 using(id)
) as t3)

のように、サブクエリのidにdistinctをつけてみてください

[naonao11]

同じ結果になってしまいます。。。
id は primary keyに設定しています。

[yambejp]

私の例示したtblを使ったsampleはうまくいくのですよね？
あとはご利用のテーブルと何処がちがうか見比べるだけでは？

Answer 3

意図した条件になっていないからですが、

特定の値が出た場合、そのid番のレコードを削除
ない場合については、idが最大のレコードを削除

というのは、
特定の値または特定の値がない場合idが最大のレコードを削除
という表現が正しいので、以下になります。

DELETE FROM cp_plan 
WHERE id = 176 
   or (
          not exists(select 1 from cp_plan where id = 176) 
      and id = (select mid from (SELECT max(id) as mid from cp_plan) as tmp)
      )

Comments

[naonao11]

ありがとうございます！
Table 'cp_plan' is specified twice, both as a target for 'DELETE' and as a separate source for data
と出てきました。。。

[sazi]

修正してみました。ただ、検証環境が無いので、同じ結果かもしれませんが。。

[naonao11]

同じエラーが出てきます。。。

[sazi]

も一度修正しました。多分maxのIDが複数あるのでしょう。
って、あっても関係ないですね。

[naonao11]

変わらずです。。。
SELECT max(id) as mid from cp_plan で確認したところ１件のみでした。
またidについてはprimary keyに設定しています。

[sazi]

エラー番号は何ですか？

[naonao11]

1093です

[sazi]

エラーについては、
https://urashita.com/archives/17713
なので、実行しているSQL文の条件にあるサブクエリーに別名を付けていないからだと思います。

[sazi]

yambejpさんは実際に実行されていますから、そのエラーが出るということは、修正内容が意図通りになっていないからだと思いますけど。