teratail
回答率
86.02%
質問するログイン新規登録

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

新規登録して質問してみよう
ただいま回答率
86.02%
トップMySQLに関する質問
MySQL

MySQL（マイエスキューエル）は、TCX DataKonsultAB社などが開発するRDBMS（リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

MySQLで、偶数件のレコードと奇数件のレコードで別々のUPDATE句を実行するにはどうすれば良いですか

qwe001
qwe001

総合スコア133

MySQL

MySQL（マイエスキューエル）は、TCX DataKonsultAB社などが開発するRDBMS（リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

2回答

0グッド

0クリップ

461閲覧

投稿2022/09/20 05:28

編集2022/09/20 05:33

現状について

現在、DBに以下のようなレコードがあります。

CREATE TABLE `logs` (
  `id` int(11) NOT NULL,
  `contents_no` varchar(10) NOT NULL,
  `page_no` smallint(5) NOT NULL,
  `day` varchar(2) NOT NULL,
  `hour` varchar(2) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DESC `logs`;
FieldTypeNullKeyDefaultExtra
idint(11)NOPRINULLauto_increment
contents_novarchar(10)NONULL
page_nosmallint(5)NONULL
dayvarchar(2)NONULL
hourvarchar(2)NONULL
createdtimestampNOCURRENT_TIMESTAMP
INSERT INTO `logs` (`id`, `contents_no`, `page_no`, `day`, `hour`, `created`) VALUES
(2840638, '22011', 1, '20', '13', '2022-09-20 13:01:13'),
(2840641, '22011', 1, '20', '13', '2022-09-20 13:02:22'),
(2840642, '22011', 2, '20', '13', '2022-09-20 13:02:34'),
(2840643, '22011', 2, '20', '13', '2022-09-20 13:05:48'),
(2840644, '22011', 3, '20', '13', '2022-09-20 13:06:12'),
(2840645, '22011', 3, '20', '13', '2022-09-20 13:11:22'),
(2840646, '22011', 4, '20', '13', '2022-09-20 13:13:13'),
(2840648, '22011', 4, '20', '13', '2022-09-20 13:15:53'),
(2840649, '22011', 5, '20', '13', '2022-09-20 13:22:03'),
(2840650, '22011', 5, '20', '13', '2022-09-20 13:35:51'),
(2840651, '22011', 6, '20', '13', '2022-09-20 13:36:00'),
(2840652, '22011', 6, '20', '13', '2022-09-20 13:38:49'),
(2840653, '22011', 7, '20', '13', '2022-09-20 13:51:11'),
(2840654, '22011', 7, '20', '13', '2022-09-20 13:59:59');

SELECT
  *
FROM
  `logs`
WHERE
  `contents_no` = '22011'
AND
  `created` BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59'
ORDER BY
  `created` ASC
;
idcontents_nopage_nodayhourcreated
284063822011120132022-09-20 13:01:13
284064122011120132022-09-20 13:02:22
284064222011220132022-09-20 13:02:34
284064322011220132022-09-20 13:05:48
284064422011320132022-09-20 13:06:12
284064522011320132022-09-20 13:11:22
284064622011420132022-09-20 13:13:13
284064822011420132022-09-20 13:15:53
284064922011520132022-09-20 13:22:03
284065022011520132022-09-20 13:35:51
284065122011620132022-09-20 13:36:00
284065222011620132022-09-20 13:38:49
284065322011720132022-09-20 13:51:11
284065422011720132022-09-20 13:59:59

やりたいこと

上記条件で抽出したレコードについて、以下の条件でUPDATEを実行したいです

  • page_no 毎に、1件目は created の値を その日の 23:59:59 に変更し、 hour の値を 23 に変更する
  • page_no 毎に、2件目は created の値を その日の 翌日の 00:00:00 に変更し、 hour の値を 00 に変更し、 day の値を 翌日に変更する
  • 【前提】抽出結果は page_no 毎に必ず2件になる。
    • 0～1件や、3件以上になることはない。
    • つまり、必ず2の倍数でレコードが抽出される。
  • 【備考】できれば、PHPなどのサーバーサイド処理を行わず、MySQL句のみでUPDATEが完結してると嬉しいです。

UPDATE後の例(現状のUPDATE句)

UPDATE `logs` SET `created` = '2022-09-20 23:59:59' AND `hour` = '23' WHERE `id` = 2840638;
UPDATE `logs` SET `created` = '2022-09-21 00:00:00' AND `hour` = '00' AND `day` = '21' WHERE `id` = 2840641;
...
UPDATE `logs` SET `created` = '2022-09-20 23:59:59' AND `hour` = '23' WHERE `id` = 2840653;
UPDATE `logs` SET `created` = '2022-09-21 00:00:00' AND `hour` = '00' AND `day` = '21' WHERE `id` = 2840654;
idcontents_nopage_nodayhourcreated
284063822011120232022-09-20 23:59:59
284064122011121002022-09-21 00:00:00
284064222011220232022-09-20 23:59:59
284064322011221002022-09-21 00:00:00
284064422011320232022-09-20 23:59:59
284064522011321002022-09-21 00:00:00
284064622011420232022-09-20 23:59:59
284064822011421002022-09-21 00:00:00
284064922011520232022-09-20 23:59:59
284065022011521002022-09-21 00:00:00
284065122011620232022-09-20 23:59:59
284065222011621002022-09-21 00:00:00
284065322011720232022-09-20 23:59:59
284065422011721002022-09-21 00:00:00

今までは、phpMyAdminから上記のSELECT句で抽出したものを、
目視でイチイチ手動で書き変えていましたが、
今回は対象行が200行近くあり、手動でやるのは骨が折れます。

作業効率を改善すべく、時々一括UPDATEできるクエリを模索していましたが、
良い書き方が思いつかないです。。

CASE句を使って、SELECT句の抽出結果を偶数行と奇数行を分ければ、
実現できそうな予感はしていますが…

何か、ドンピシャなクエリありませんか？
よろしくお願いします。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

下記のような質問は推奨されていません。

  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

回答2件

1

SQL
1update logs as t1
2set created=date_format(created,'%Y-%m-%d 23:59:59')
3where not exists(select 1 from logs where page_no=t1.page_no and id<t1.id);
4
5update logs as t1
6set created=date_format(created,'%Y-%m-%d 00:00:00')+interval 1 day
7where not exists(select 1 from logs where page_no=t1.page_no and id>t1.id);

一発でやるならこんな感じ？
dayとhourも修正対象だったので調整

SQL
1update logs as t1
2set created=date_format(created,'%Y-%m-%d 23:59:59') + interval
3coalesce((select 1 from logs where page_no=t1.page_no and id<t1.id),0) second
4,day=date_format(created+ interval
5coalesce((select 1 from logs where page_no=t1.page_no and id<t1.id),0) day,'%d')
6,hour=coalesce((select '00' from logs where page_no=t1.page_no and id<t1.id),'23')
7

範囲指定あり

バージョンの違いを吸収

SQL
1update logs as t1, logs as t2
2set t1.created=date_format(t2.created,'%Y-%m-%d 23:59:59') + interval
3coalesce((select 1 from (select * from logs) as t3 where page_no=t2.page_no and id<t2.id),0) second
4,t1.day=date_format(t2.created+ interval
5coalesce((select 1 from (select * from logs) as t3  where page_no=t2.page_no and id<t2.id),0) day,'%d')
6,t1.hour=coalesce((select '00' from (select * from logs) as t3  where page_no=t2.page_no and id<t2.id),'23')
7where 
8t1.id=t2.id 
9and t1.contents_no = '22011'
10and t1.created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59'
11

投稿2022/09/20 05:40

編集2022/09/20 10:23
yambejp
yambejp

総合スコア109850

qwe001が👍を押しています

下記のような回答は推奨されていません。

  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

qwe001
qwe001

2022/09/20 06:37

早速のご回答ありがとうございます。 SELECT句にあるように、contents_noとcreatedの範囲で絞り込んだうえでUPDATEしたいのですが、どのように調整すると良いでしょうか。上記のUPDATE句ですと、全てのIDが書き変え対象になるので、関係のないレコードまで書き変えてしまいます。
yambejp
yambejp

2022/09/20 06:48 編集

サンプルが対象分しかないので検証ができませんが一応追記しておきます たとえば奇数データが13時前だったり、偶数のデータが14時後だったりするとき 奇数偶数両方合致しないといけないのか、片方しか条件合致しないなら両方とも対象外にしたいのか曖昧ですね 具体的にこういうデータのときはこれが対象（もしくは対象外）という例示があるとよいでしょう
qwe001
qwe001

2022/09/20 10:02 編集

ご返信ありがとうございます。サブクエリの中で同じテーブルは指定できないようで、上記のクエリを実行すると右記のエラーが表示されます。 `You can't specify target table 't1' for update in FROM clause` サブクエリ内を `SELECT 1 FROM ( SELECT page_no, id FROM logs AS t1 WHERE contents_no = '22011' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59' ) AS temp1 WHERE page_no = t1.page_no AND id < t1.id` のように置き換えれば実行できそうです。 --一点、教えてほしいのですが、createdカラムのUPDATE句の末尾にある `second` は一体何を意味しますか？ `AS second` のようなエイリアス句でしょうか。-- すみません、理解しました。秒を足すということですね。条件に一致すれば、INTERVAL +1 secondすることで、翌日の00:00:00にUPDATEし、一致しなければINTERVAL +0 secondで、当日の23:59:59にUPDATEすると。
yambejp
yambejp

2022/09/20 10:23

mysql 5.7系と8系だと処理が違うっぽいですね修正版を上げておきます なおsecondは- intervar 〇〇 secondのことです
qwe001
qwe001

2022/09/21 08:59

失礼しました。MySQLのバージョンを記載しておりませんでした。ご推察の通り、MySQL v5.7.33-log になります。 修正頂いたクエリでもやりたいことが実現できました。私が書いたクエリよりも書き換えが必要な個所が少ないので、こちらを今後は使っていきたいと思います。大変助かりました。ご回答ありがとうございます。

0

自己解決

@yambejp 様
ご提示いただきましたSQL文を参考にして、以下のクエリでやりたいことが実現できました！
結局手動で1件ずつUPDATEするよりも時間はかかってしまいましたが、これからの作業はぐんと楽になるはずですし、COALESCE句を知らなかったので良い勉強になりました。
ご回答ありがとうございました。

-- 以下のSQLクエリは、3カラムの処理をひとつにまとめたものである
-- @see https://teratail.com/questions/3yc3d9blff2gso
-- [created] page_no毎に、抽出結果の1件目は、当日の23:59:59にUPDATEする UPDATE `logs` SET `created` = DATE_FORMAT(`created`, '%Y-%m-%d 23:59:59' ) + INTERVAL 0 SECOND WHERE contents_no = '00000000' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59';
-- [created] page_no毎に、抽出結果の2件目は、翌日の00:00:00にUPDATEする UPDATE `logs` SET `created` = DATE_FORMAT(`created`, '%Y-%m-%d 23:59:59' ) + INTERVAL 1 SECOND WHERE contents_no = '00000000' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59';
-- [day] page_no毎に、抽出結果の1件目は、当日の値にUPDATEする           UPDATE `logs` SET `day` = `day` + 0 WHERE contents_no = '00000000' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59';
-- [day] page_no毎に、抽出結果の2件目は、翌日の値にUPDATEする           UPDATE `logs` SET `day` = `day` + 1 WHERE contents_no = '00000000' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59';
-- [hour] page_no毎に、抽出結果の1件目は、'23' にUPDATEする             UPDATE `logs` SET `hour` = '23' WHERE contents_no = '00000000' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59';
-- [hour] page_no毎に、抽出結果の2件目は、'00' にUPDATEする             UPDATE `logs` SET `hour` = '00' WHERE contents_no = '00000000' AND created BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59';

UPDATE
        `logs` AS t1
    SET
        `created` = DATE_FORMAT(`created`, '%Y-%m-%d 23:59:59' ) + INTERVAL COALESCE (
            (
                SELECT
                        1
                    FROM
                        (
                            SELECT
                                    `page_no`, `id`
                                FROM
                                    `logs` AS t1
                                WHERE
                                    `contents_no` = '22011'
                                  AND
                                    `created` BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59'
                        ) AS `temp1`
                    WHERE
                        `page_no` = t1.page_no
                      AND
                        `id` < t1.id
            ), 0
        ) SECOND,
        `day` = DAY + COALESCE (
            (
                SELECT
                        1
                    FROM
                        (
                            SELECT
                                    `page_no`, `id`
                                FROM
                                    `logs` AS t1
                                WHERE
                                    `contents_no` = '22011'
                                  AND
                                    `created` BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59'
                        ) AS `temp1`
                    WHERE
                        `page_no` = t1.page_no
                      AND
                        `id` < t1.id
            ), 0
        ),
        `hour` = COALESCE (
            (
                SELECT
                        '00'
                    FROM
                        (
                            SELECT
                                    `page_no`, `id`
                                FROM
                                    `logs` AS t1
                                WHERE
                                    `contents_no` = '22011'
                                  AND
                                    `created` BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59'
                        ) AS `temp1`
                    WHERE
                        `page_no` = t1.page_no
                      AND
                        `id` < t1.id
            )
            , '23'
        )
    WHERE
        `contents_no` = '22011'
      AND
        `created` BETWEEN '2022-09-20 13:00:00' AND '2022-09-20 13:59:59'
;

投稿2022/09/20 11:06

qwe001
qwe001

総合スコア133

下記のような回答は推奨されていません。

  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

15分調べてもわからないことは
teratailで質問しよう！

ただいまの回答率
86.02%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL（マイエスキューエル）は、TCX DataKonsultAB社などが開発するRDBMS（リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

トップMySQLに関する質問

MySQLで、偶数件のレコードと奇数件のレコードで別々のUPDATE句を実行するにはどうすれば良いですか