各DBのUPDATE文でJOIN句とCASE式を使ってみた｜ITエンジニアとして経験・学習したこと

SQLの構文として、テーブル結合をするためのJOIN句や、条件分岐を表現するためのCASE式があるが、これらはSELECT文だけでなく、UPDATE文でも利用できる。

今回は、Oracle、MySQL、PostgreSQL、SQL Serverの各DB上で、JOIN句とCASE式を含むSQL(SELECT文・UPDATE文)を実行してみたので、その結果を共有する。

前提条件

下記記事のように、A5M2を利用して各DBに接続できていること。

A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...

また、各DB上に、以下のデータが設定されていること。(下図はOracleの例)

SELECT * FROM user_data

1	SELECT * FROM user_data

SELECT * FROM m_sex

1	SELECT * FROM m_sex

Oracleの場合の実行結果

Oracle上でJOIN句とCASE式を含むSQL(SELECT文・UPDATE文)の実行結果は、以下の通り。

1) user_dataテーブル、m_sexテーブルを結合し、CASE式を利用したSELECT文は、以下の通り。

SELECT 
     u.*
   , CASE 
     WHEN u.birth_month = 12 
       OR u.birth_month BETWEEN 1 AND 2
       THEN '冬生まれ：' || s.sex_value || '性'
     WHEN u.birth_month BETWEEN 3 AND 5
       THEN '春生まれ：' || s.sex_value || '性'
     WHEN u.birth_month BETWEEN 6 AND 8
       THEN '夏生まれ：' || s.sex_value || '性'
     WHEN u.birth_month BETWEEN 9 AND 11
       THEN '秋生まれ：' || s.sex_value || '性'
     ELSE
       '生年月日_月が不正'
     END AS memo_aft
FROM user_data u 
INNER JOIN m_sex s
ON u.sex = s.sex_cd
ORDER BY u.id ASC

SELECT

u.*

, CASE

WHEN u.birth_month = 12

OR u.birth_month BETWEEN 1 AND 2

THEN '冬生まれ：' || s.sex_value || '性'

WHEN u.birth_month BETWEEN 3 AND 5

THEN '春生まれ：' || s.sex_value || '性'

WHEN u.birth_month BETWEEN 6 AND 8

THEN '夏生まれ：' || s.sex_value || '性'

WHEN u.birth_month BETWEEN 9 AND 11

THEN '秋生まれ：' || s.sex_value || '性'

ELSE

'生年月日_月が不正'

END AS memo_aft

FROM user_data u

INNER JOIN m_sex s

ON u.sex = s.sex_cd

ORDER BY u.id ASC

2) user_dataテーブル、m_sexテーブルを結合し、CASE式を利用したUPDATE文は、以下の通り。

UPDATE
    (
        SELECT
             u.birth_month
           , u.memo
           , s.sex_value
        FROM user_data u
        INNER JOIN m_sex s
            ON u.sex = s.sex_cd
    )
SET
    memo = (
       CASE
          WHEN birth_month = 12
                OR birth_month BETWEEN 1 AND 2
              THEN '冬生まれ：' || sex_value || '性'
          WHEN birth_month BETWEEN 3 AND 5
              THEN '春生まれ：' || sex_value || '性'
          WHEN birth_month BETWEEN 6 AND 8
              THEN '夏生まれ：' || sex_value || '性'
          WHEN birth_month BETWEEN 9 AND 11
              THEN '秋生まれ：' || sex_value || '性'
          ELSE
               '生年月日_月が不正'
          END
    )

UPDATE

(

SELECT

u.birth_month

, u.memo

, s.sex_value

FROM user_data u

INNER JOIN m_sex s

ON u.sex = s.sex_cd

)

SET

memo = (

CASE

WHEN birth_month = 12

OR birth_month BETWEEN 1 AND 2

THEN '冬生まれ：' || sex_value || '性'

WHEN birth_month BETWEEN 3 AND 5

THEN '春生まれ：' || sex_value || '性'

WHEN birth_month BETWEEN 6 AND 8

THEN '夏生まれ：' || sex_value || '性'

WHEN birth_month BETWEEN 9 AND 11

THEN '秋生まれ：' || sex_value || '性'

ELSE

'生年月日_月が不正'

END

)

上記UPDATE文を実行すると、以下のように、左下のコンソール上に更新した件数が表示されることが確認できる。

3) 2)のUPDATE文実行後の、 user_dataテーブルの値は以下の通りで、記載通りの内容で更新されていることが確認できる。

SELECT * FROM user_data

1	SELECT * FROM user_data

「Envader」はLinuxコマンドやDatabase SQL等のスキルを、環境構築不要で習得できる学習サイトだった「Envader」は、ITエンジニアとしてよく使うLinuxコマンドやDatabase SQL等のスキルを、解説を読んだ上で、問題を解き...

MySQLの場合の実行結果

MySQL上でJOIN句とCASE式を含むSQL(SELECT文・UPDATE文)の実行結果は、以下の通り。

1) user_dataテーブル、m_sexテーブルを結合し、CASE式を利用したSELECT文は、以下の通り。

SELECT 
     u.*
   , CASE 
     WHEN u.birth_month = 12 
       OR u.birth_month BETWEEN 1 AND 2
       THEN CONCAT('冬生まれ：', s.sex_value, '性')
     WHEN u.birth_month BETWEEN 3 AND 5
       THEN CONCAT('春生まれ：', s.sex_value, '性')
     WHEN u.birth_month BETWEEN 6 AND 8
       THEN CONCAT('夏生まれ：', s.sex_value, '性')
     WHEN u.birth_month BETWEEN 9 AND 11
       THEN CONCAT('秋生まれ：', s.sex_value, '性')
     ELSE
       '生年月日_月が不正'
     END AS memo_aft
FROM user_data u 
INNER JOIN m_sex s
ON u.sex = s.sex_cd
ORDER BY u.id ASC

SELECT

u.*

, CASE

WHEN u.birth_month = 12

OR u.birth_month BETWEEN 1 AND 2

THEN CONCAT('冬生まれ：', s.sex_value, '性')

WHEN u.birth_month BETWEEN 3 AND 5

THEN CONCAT('春生まれ：', s.sex_value, '性')

WHEN u.birth_month BETWEEN 6 AND 8

THEN CONCAT('夏生まれ：', s.sex_value, '性')

WHEN u.birth_month BETWEEN 9 AND 11

THEN CONCAT('秋生まれ：', s.sex_value, '性')

ELSE

'生年月日_月が不正'

END AS memo_aft

FROM user_data u

INNER JOIN m_sex s

ON u.sex = s.sex_cd

ORDER BY u.id ASC

2) user_dataテーブル、m_sexテーブルを結合し、CASE式を利用したUPDATE文は、以下の通り。

UPDATE user_data u 
INNER JOIN m_sex s
ON u.sex = s.sex_cd
SET u.memo = (
    CASE
    WHEN u.birth_month = 12 
       OR u.birth_month BETWEEN 1 AND 2
       THEN CONCAT('冬生まれ：', s.sex_value, '性')
   WHEN u.birth_month BETWEEN 3 AND 5
       THEN CONCAT('春生まれ：', s.sex_value, '性')
   WHEN u.birth_month BETWEEN 6 AND 8
       THEN CONCAT('夏生まれ：', s.sex_value, '性')
   WHEN u.birth_month BETWEEN 9 AND 11
       THEN CONCAT('秋生まれ：', s.sex_value, '性')
    ELSE
       '生年月日_月が不正'
    END
)

UPDATE user_data u

INNER JOIN m_sex s

ON u.sex = s.sex_cd

SET u.memo = (

CASE

WHEN u.birth_month = 12

OR u.birth_month BETWEEN 1 AND 2

THEN CONCAT('冬生まれ：', s.sex_value, '性')

WHEN u.birth_month BETWEEN 3 AND 5

THEN CONCAT('春生まれ：', s.sex_value, '性')

WHEN u.birth_month BETWEEN 6 AND 8

THEN CONCAT('夏生まれ：', s.sex_value, '性')

WHEN u.birth_month BETWEEN 9 AND 11

THEN CONCAT('秋生まれ：', s.sex_value, '性')

ELSE

'生年月日_月が不正'

END

)

上記UPDATE文を実行すると、以下のように、左下のコンソール上に更新した件数が表示されることが確認できる。

3) 2)のUPDATE文実行後の、 user_dataテーブルの値は以下の通りで、記載通りの内容で更新されていることが確認できる。

SELECT * FROM user_data

1	SELECT * FROM user_data

Androidロックを解除する裏ワザ「4uKey for Android」をご紹介Android端末では、以下の画像のような画面ロックパスワードを設定することができますが、このパスワードを忘れてしまうと、Android...

PostgreSQLの場合の実行結果

PostgreSQL上でJOIN句とCASE式を含むSQL(SELECT文・UPDATE文)の実行結果は、以下の通り。

1) user_dataテーブル、m_sexテーブルを結合し、CASE式を利用したSELECT文は、以下の通り。

SELECT 
     u.*
   , CASE 
     WHEN u.birth_month = 12 
       OR u.birth_month BETWEEN 1 AND 2
       THEN '冬生まれ：' || s.sex_value || '性'
     WHEN u.birth_month BETWEEN 3 AND 5
       THEN '春生まれ：' || s.sex_value || '性'
     WHEN u.birth_month BETWEEN 6 AND 8
       THEN '夏生まれ：' || s.sex_value || '性'
     WHEN u.birth_month BETWEEN 9 AND 11
       THEN '秋生まれ：' || s.sex_value || '性'
     ELSE
       '生年月日_月が不正'
     END AS memo_aft
FROM user_data u 
INNER JOIN m_sex s
ON u.sex = s.sex_cd
ORDER BY u.id ASC

SELECT

u.*

, CASE

WHEN u.birth_month = 12

OR u.birth_month BETWEEN 1 AND 2

THEN '冬生まれ：' || s.sex_value || '性'

WHEN u.birth_month BETWEEN 3 AND 5

THEN '春生まれ：' || s.sex_value || '性'

WHEN u.birth_month BETWEEN 6 AND 8

THEN '夏生まれ：' || s.sex_value || '性'

WHEN u.birth_month BETWEEN 9 AND 11

THEN '秋生まれ：' || s.sex_value || '性'

ELSE

'生年月日_月が不正'

END AS memo_aft

FROM user_data u

INNER JOIN m_sex s

ON u.sex = s.sex_cd

ORDER BY u.id ASC

2) user_dataテーブル、m_sexテーブルを結合し、CASE式を利用したUPDATE文は、以下の通り。

UPDATE user_data
SET memo =
    CASE WHEN birth_month = 12
       OR birth_month BETWEEN 1 AND 2
       THEN '冬生まれ：' || m_sex.sex_value || '性'
    WHEN birth_month BETWEEN 3 AND 5
       THEN '春生まれ：' || m_sex.sex_value || '性'
    WHEN birth_month BETWEEN 6 AND 8
       THEN '夏生まれ：' || m_sex.sex_value || '性'
    WHEN birth_month BETWEEN 9 AND 11
       THEN '秋生まれ：' || m_sex.sex_value || '性'
    ELSE
       '生年月日_月が不正'
    END
FROM m_sex
WHERE
    user_data.sex = m_sex.sex_cd