Help us understand the problem. What is going on with this article?

[SQLite3] 列の値を一括変換する

More than 1 year has passed since last update.

とある初心者の備忘録です。

ある列の値を一括で置き換えたい場合のクエリ。UPDATE時に SET col = value のvalueの部分を動的に持ってきたいケースです。

例えば、usersテーブルに部署(dept)を名前でベタ書きしてしまっているテーブルについて(そんな初歩的な設計ミスをするなよ、という話は棚上げします・・・)。

後から部署テーブル(departments)の定義を行ったので、usersテーブルにあるdept列は全てIDで置き換えたい、というケースです。
SET の中にサブクエリを書けるので、この要件を満たすクエリは以下の1本で書くことが可能です。

UPDATE
    users
SET    users.dept    =    (
        SELECT
            departments.id
        FROM
            departments
        WHERE
            users.dept    =    users.dept
    )
;

SQL勉強中なので、ネタができたら随時書き足していく予定。

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away