テーブルAのカラムXが特定の値の場合と、テーブルBにある日付がテーブルAに存在する場合、テーブルAのカラムY,Zを更新したい

Question

現在Servlet/JSP、MariaDB(MySQLの文法で動きます)で予約機能のあるサイトを作っています。
DBで以下の操作をしたいのですが、UPDATE文が３つに別れています。
１つの文で書くことは可能でしょうか？

◆やりたいこと
営業状況と予約状況を管理するテーブル「schedule」と、祝日や臨時休業の日時を登録するテーブル「yasumi」があり、
毎週月曜日とテーブル「yasumi」にある日付をテーブル「schedule」では休業日の扱いにしたい。
※テーブル「schedule」のeigyouカラムが1のとき休業

↓

◆各テーブルとカラムの内容
テーブル「schedule」：営業状況と予約状況を登録
dateカラム：日付
dowカラム：dayofweek(date) 曜日の取得 dow=2のとき休業日(eigyou=1)にしたい
eigyouカラム：0(デフォルト)のとき営業、1のとき休業

テーブル「yasumi」祝日や臨時休業の日時を登録
dateカラム：休業日 テーブル「schedule」のdateと連動
contentカラム：休みの内訳（臨時休業など)

◆現在使用しているSQL文
【SQL1】dowの値によってyoubiの値を更新する

UPDATE `test`.`schedule` SET youbi = 
(CASE dow
	WHEN 1 THEN '（日）'
	WHEN 2 THEN '（月）'
	WHEN 3 THEN '（火）'
	WHEN 4 THEN '（水）'
	WHEN 5 THEN '（木）'
	WHEN 6 THEN '（金）'
	WHEN 7 THEN '（土）'
	ELSE null
END)

　

【SQL2】test.yasumiの日付がtest.scheduleにある場合、test.yasumiのyoubiをtest.scheduleのcontentの値に置き換え、eigyouの値を１にする

UPDATE `test`.`schedule` t1, `test`.`yasumi` t2
SET t1.youbi = t2.content, 
t1.eigyou = 1 
WHERE t1.date = t2.date

　

【SQL3】月曜日を休みにする

UPDATE `test`.`schedule` t1 
SET t1.eigyou = 1
WHERE t1.dow = 2

　
分かりづらい文章になってしまいましたが回答お待ちしております。

Comments

[jimbe]

> テーブル「schedule」：営業状況と予約状況を登録

1 つのテーブルで 2 つの状況を管理しようとしている点が気になります。
祝日・臨時休業が yasumi に入っていてその情報が必要であれば、 schedule
を select する際に yasumi を繋げれば良いように思いますが、だめなのでしょうか。

> 月曜日を休みにする

schedule に insert する段階で分かることかと思うのですが、なぜ後から update する形なのでしょう。

Answer 1

「scheduleテーブルのdateが月曜日の場合、もしくは、yasumiテーブルにそのdateがある場合、scheduleテーブルのeigyouカラムを1にしたい」というのがやりたいことであっていますか?

あっているのであれば、以下のようなSQLで実現可能かと思います。

UPDATE schedule S
SET eigyou = 1
WHERE DAYOFWEEK(date) = 2 -- 月曜日
OR EXISTS (
  SELECT *
  FROM yasumi Y
  WHERE S.date = Y.date
);

Comments

[nechikoyan]

UPDATE schedule S
SET youbi = (SELECT content FROM yasumi Y WHERE S.date = Y.date),
eigyou = 1
WHERE DAYOFWEEK(date) = 2 -- 月曜日
OR EXISTS (
SELECT content
FROM yasumi Y
WHERE S.date = Y.date
)

休業日のyoubiをyasumi.contentの値で更新し、休業日(yasumiに登録した日時と月曜日)のeigyouの値を１にすることが出来ました！
営業日の曜日はyoubiの値がnullだった場合、DAO内でdowの値をもとに曜日を設定してDTOに受け渡すことにします。
ありがとうございました。

[neko_the_shadow]

「eigyouカラムと同時にyoubiカラムも更新したい、かつ、youbiカラムはcontentカラムがnullの場合のみ更新したい」ということであれば、JOINを使ってもよいかもしれませんね

UPDATE schedule S
LEFT OUTER JOIN yasumi Y ON S.date = Y.date
SET eigyou = 1, youbi = COALESCE(Y.content, S.youbi)
WHERE DAYOFWEEK(S.date) = 2
OR Y.date IS NOT NULL

Answer 2

要件や条件が違うため、1つにすることは困難です。

強いてなら、これって本当にDBに持たなきゃいけない？くらいでしょうか。
その後の用途不明ですがJavaから利用されるなら表示時のロジック（もしくはSELECTで）でも判定できそうに思います。
祝日は判定するライブラリがありますし、臨時休業とか店舗独自のモノだけDBに持っておけば良いと思うのですが。

Comments

[nechikoyan]

yasumiに登録した休業日の内訳だけschedule.youbiに記載することにしました。
営業日の曜日はyoubiの値がnullだった場合、DAO内でdowの値をもとに曜日を設定してDTOに受け渡すことにします。
祝日の設定方法いくつかあるようなので検討してみます。
回答ありがとうございました。