他テーブルを前方一致検索した結果にてデータ更新するSQLの書き方

Question

SQLについてご教授願います。

上記のような2つのテーブルがあります。
clinicには医院情報が2000件程入っています。
cityには全国の市区町村データが1800件程入っています。

clinicのaddressで、cityのcitynameを前方一致検索して得られたcityのcityidを、
clinicのcityidにセットしたいです。

なお、clinicのcityidには古いデータが入っているため、UPDATEになると考えています。

どうぞよろしくお願いいたします。

Answer 1

まさかのワイルドカードとか考えたくなかったので、INSTR使ってみました。

UPDATE
    clinic a,
    city b
SET 
    a.cityid = b.cityid
WHERE
  INSTR(a.address, b.cityname) = 1
;

Comments

[PenelopeG]

ありがとうございます。上手くいきました！！！
前方一致だと思い込んでしまうあたり、まだまだ私は未熟です(汗
勉強になりました。

---------------
WHERE句だけ以下のとおり修正しました。（後で見たときのため一応・・・）
INSTR(a.address, b.cityname) = 1

[shoko1]

あらあら、未実行だとやらかしますね、すみません。回答のほうも直しておきました。

[PenelopeG]

ありがとうございますm(_ _)m

Answer 2

MySQL 限定だと思いますが。

UPDATE
    clinic t1,
    city t2
SET 
    t1.cityid = t2.cityid
WHERE
    t1.address like t2.cityname||'%';
LIMIT 1

ただ like で複数引っかかる可能性があるので、どの順でマッチするかは分かりません。

だいぶズブズブな感じがするので、よく検討されてからの方が良さそうです。

Comments

[mattn]

あとマスタテーブルなのでバックアップしてから実行した方が良いです。

[PenelopeG]

さっそくのご回答ありがとうございます。
試してみましたが、
#1221 - Incorrect usage of UPDATE and LIMIT
と、エラーが出てしまいました。
複数テーブルを扱っているからでしょうか？

そこで、LIMIT 1を除いて実行してみたものの、1件も変更されませんでした。。。
恐縮です。