• ベストアンサー
  • すぐに回答を!

MySQLでWHEN句のサブクエリ中にて

MySQL ver.5.0.95です。 PHPからSQL文を作ってDBへインサートしているのですが 重複した値が合った場合はupdateするように書いています。 TBL1には |id   |string   |count 1    aaa    0 2    bbb    3 3    ccc    1 TBL2には |id2    |string2    |count2 1      aaa      0 2      bbb      2 3      ccc      1 実際はもう少し複雑ですが、こういう感じでデータが入ってるとします。 このとき、TBL1のそれぞれをインサート若しくはアップデートするのに TBL2の同一IDのものの、count2の状態によって場合分けしたく 同一IDのcount2が1以上なら変更しない、という風にするため以下のように書いたのですが insert into TBL1 values (1, "aaa", 3), (2, "bbb", 3), (3, "ccc", 3), (4, "ddd", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = values(id)) >=1 THEN count ELSE count + values(count) END; #1064 - You have an error in your SQL syntax;というエラーになって出来ません。 WHENの中のサブクエリで、where id2 = values(id) 恐らくこの文がダメなようです。 例えば決め撃ちで1つ1つ書くと通ります。 insert into TBL1 values(1, "aaa", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 1) >=1 THEN count ELSE count + values(count) END; insert into TBL1 values(2, "bbb", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 2) >=1 THEN count ELSE count + values(count) END; insert into TBL1 values(3, "ccc", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 3) >=1 THEN count ELSE count + values(count) END; insert into TBL1 values(4, "ddd", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 4) >=1 THEN count ELSE count + values(count) END; これをエラーになったような文に変えて1文にまとめたいのですが、無理でしょうか? あとそもそもこれをまとめたほうが速いと思ってそうしたいのですが、 変わらないのなら1文1文;でつなげて書くやり方でいこうと思っています。 まとめると速くなることはあるでしょうか?

共感・応援の気持ちを伝えよう!

  • 回答数1
  • 閲覧数281
  • ありがとう数0

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1
  • yambejp
  • ベストアンサー率51% (3827/7415)

バルク処理でupdateまでやってしまうとなると一度テンポラリテーブルを 作った方がよいかも //元データ create table TBL1(id int not null unique key,string varchar(10),count int); insert into TBL1 values(1,'aaa',0),(2,'bbb',3),(3,'ccc',1); create table TBL2(id2 int not null unique key,string2 varchar(10),count2 int); insert into TBL2 values(1,'aaa',0),(2,'bbb',2),(3,'ccc',1); //処理 create temporary table tmp(id3 int,string3 varchar(10),count3 int); insert into tmp values (1,'aaa',3),(2,'bbb',3),(3,'ccc',3),(4,'ddd',3); insert into TBL1 select id3,string3,count3 from tmp left join TBL2 on id3=id2 where count2 is null or count2<1 on duplicate key update count=count3; //テンポラリを使わない場合はunionでデータを羅列? insert into TBL1 select id3,string3,count3 from (select 1 as id3,'aaa' as string3,3 as count3 union select 2,'bbb',3 union select 3,'ccc',3 union select 4,'ddd',3) as list left join TBL2 on id3=id2 where count2 is null or count2<1 on duplicate key update count=count3;

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • MYSQLとPHPによって取得する多次元配列

    はじめてご質問させていただきます。 現在以下のようなテーブルが3つあります。 [ tbl1 ] | tbl1Key | title | | 1 | a | | 2 | b | | 3 | c | | 4 | d | [tbl2] | tbl2Key | tbl1Key | tbl3Key | | 1 | 2 | 2 | | 2 | 3 | 1 | | 3 | 3 | 4 | | 4 | 1 | 3 | [tbl3] | tbl3Key | value | | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ddd | 上記のデータソースを元に以下のような結果を得たいと考えています。 array( array( 'tbl1Key'=>1, 'title'=>'a', 'values' => array( array( 'tbl3Key'=>3, value='ccc' ) ), array( 'tbl1Key'=>2, 'title'=>'b', 'values' => array( array( 'tbl3Key'=>2, value='bbb' ) ), array( 'tbl1Key'=>3, 'title'=>'c', 'values' => array( array( 'tbl3Key'=>1, value='aaa'), array( 'tbl3Key'=>4, value='ddd' ) ), array( 'tbl1Key'=>4, 'title'=>'d', 'values' = > array( array( 'tbl3Key'=> , value='' ) ) ) この場合phpによって $sql = ("select * from `tbl1`"); $res = mysql_query( $sql, $con ); $data = array(); while( $row =mysql_fetch_object( $res ) ) { $sql = ("select * from `tbl3` inner join `tbl2` on `tbl3`.`tbl3Key`=`tbl2`.`tbl3Key` where `tbl2`.`tbl1Key`={$row->tbl1Key}"); $res2 = mysql_query( $sql, $con ); $tmp = array(); while( $r = mysql_fetch_object( $res2 ) ) { $tmp[] = $r->value; } $row->values = $tmp; $data = $row; } まずtbl1のデータをすべて取り、配列dataに格納する段階で、tbl1と関連付けされたtbl3のvalueを取得し配列tblに格納、それを新しい要素として配列dataにプッシュしている状況です。 こういったテーブルでいうところの1カラムのみ配列で返すような事は、こういったループを使って以外にも可能なのでしょうか。 よろしくお願いいたします。

    • 締切済み
    • PHP
  • 結合で重複のエラーが出る

    mysql5.1.33を使用しています。 次のような構成で、 テーブル「tbl1s」 id int(11) テーブル「tbl2s」 id int(11) テーブル「tbl1s_tbl2s」 id int(11) tbl1_id int(11) tbl1sの外部キー tbl2_id int(11) tbl2sの外部キー テーブル「tbl2sbelongs」 id int(11) tbl2_id int(11) tbl2sの外部キー count int(11) テーブル「tbl1s」 +------+ |  id  | +------+ |  1  | +------+ |  2  | +------+ |  3  | +------+ テーブル「tbl2s」 +------+ |  id  | +------+ |  1  | +------+ |  2  | +------+ |  3  | +------+ テーブル「tbl1s_tbl2s」 +------+-------+-------+ |  id  | tbl1_id | tbl2_id | +------+-------+-------+ |  1  | 1    | 1   | +------+-------+------+ |  2  | 1    | 2   | +------+-------+------+ |  3  | 3    | 3   | +------+-------+------+ テーブル「tbl2sbelongs」 +------+-------+-------+ |  id  | tbl2_id | count | +------+-------+-------+ |  1  | 1    | 3   | +------+-------+------+ |  2  | 2    | 10  | +------+-------+------+ |  3  | 3    | 15  | +------+-------+------+ SELECT * FROM `tbl1s_tbl2s` AS `Tbl1sTbl2` INNER JOIN (SELECT *, sum(Tbl2sbelong.`count`) as sum FROM `tbl2s` AS `Tbl2` INNER JOIN `tbl2sbelongs` AS `Tbl2sbelong` ON (`Tbl2`.`id`=`Tbl2sbelong`.`tbl2_id`) WHERE 1 = 1 GROUP BY `Tbl2`.`id` ) AS `SUB` ON (`Tbl1sTbl2`.`tbl2_id`=`SUB`.`id`) WHERE 1 = 1 GROUP BY `tbl1_id` を実行すると、 「SQL Error: 1060: Duplicate column name 'id' 」 というエラーが出てしまいます・・・ おそらくカラムのidが結合の中で重複しているという内容だと思うのですが、 3つもテーブルが絡んでくるとどれのidがというのが分からないのですが、 これは何が原因なのでしょうか? ちなみにサブクエリの SELECT *, sum(Tbl2sbelong.`count`) as sum FROM `tbl2s` AS `Tbl2` INNER JOIN `tbl2sbelongs` AS `Tbl2sbelong` ON (`Tbl2`.`id`=`Tbl2sbelong`.`tbl2_id`) WHERE 1 = 1 GROUP BY `Tbl2`.`id` この段階までは実行できます。 最終的には、「tbl2sbelongs」テーブルの「count」の値によって、「tbl1s」を降順に並べ替えたいと思っています。 この例だと テーブル「tbl1s」 +------+ |  id  | +------+ |  3  |count = tbl2_id「3」の15 = 15 +------+ |  1  |count = tbl2_id「1」の3 + tbl2_id「2」の10 = 13 +------+ |  2  |count = 0 +------+

    • ベストアンサー
    • MySQL
  • SQL サブクエリで抽出したカラムについて

    サブクエリで抽出したカラムを 本クエリでの結果と一緒に表示したいのですが どのように実現してよいかわからず困っております。 詳細としては、下記のようなことをしたいと考えております。 文中の1)と2)は文章下部にあるSQLを指しております ----------------------------------------------------------- 最初は、1)のように凄く単純なSQLでよかったのですが 仕様が変わり、TBL2で取得した新しいカラムも一緒に 本クエリでの結果として取得できれば、SQLを2回実行せずに済むなと考えたのですが、 2)のように色々と試行錯誤したのですが、実現できません。 (2)は単なる空想ですが…) そこで、わかる方にお伺いしたいのですが、 副問い合わせで取得した複数カラムの中の1つのカラム【2)でいうところのTBL2.X】 を 2)のように"select TBL1.A , TBL2.X"として、結果を得ることは可能でしょうか。 ただし、 TBL1のカラムAは、TBL2に存在しない TBL2のカラムXは、TBL1には存在しない カラムBは、TBL1、TBL2に存在する の条件となっております 環境はMysqlです テーブル結合の方法も考えたのですが、 TBL2で、日付でソートし、その上位100件だけを 持ってきたいと思ったので、副問い合わせで一気に抽出しようとこの方法を考えまし た お分かりになる方がいらっしゃいましたらよろしくお願いします 1)SQL ------------ select TBL1.A from TBL1 where TBL1.B in ( select TBL2.B from TBL2 where 条件 ) 2)想像SQL(こんな感じの事をしたい) -------------- select TBL1.A , TBL2.X from TBL1 where TBL1.B in ( select TBL2.B , TBL2.X from TBL2 where 条件 order by TBL2.日付 desc limit 100 )

    • ベストアンサー
    • MySQL
  • 表の結合と並べ替え

    次のようなテーブル構成で、 CREATE TABLE tbl1 ( id int PRIMARY KEY AUTO_INCREMENT ) ENGINE=InnoDB; CREATE TABLE tbl2 ( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) ) ENGINE=InnoDB; CREATE TABLE tbl1_tbl2 ( id int PRIMARY KEY AUTO_INCREMENT, tbl1_id int, tbl2_id int, FOREIGN KEY(tbl1_id) REFERENCES tbl1(id), FOREIGN KEY(tbl2_id) REFERENCES tbl2(id) ) ENGINE=InnoDB; CREATE TABLE tbl3 ( id INT PRIMARY KEY AUTO_INCREMENT, tbl1_id int, created datetime, FOREIGN KEY(tbl1_id) REFERENCES tbl1(id) ) ENGINE=InnoDB; [tbl1] id 1 2 3 [tbl2] id  name 1   a 2   b [tbl1_tbl2] id  tbl1_id  tbl2_id 1   1      1 2   1      2 3   1      2 4   2      1 5   3      1 6   3      2 [tbl3] id  tbl1_id  created 1   1    2011-10-17 01:51:39 2   1    2011-10-17 02:51:39 3   3    2000-10-17 01:51:39 ここから次のような「tbl1のid」「tbl2の個数」「tbl3の最新のcreated」を基準とした (下の例は「tbl1のid」を昇順にした結果です) それぞれの並べ替た結果を得たいのですが、どのようなSQL文を記述すれば良いのでしょうか? [tbl1のid] [tbl2のnameのリスト] [tbl3の最新のcreated]   1         a,b,b      2011-10-17 02:51:39   2         a           3         a,b       2000-10-17 01:51:39

    • ベストアンサー
    • MySQL
  • MicrosoftACCESSの更新クエリがわかる方!

    こんにちはtmgmです。 ACCESSの更新クエリで質問です。 今、TBLとTBL1という二つのテーブルがあります。TBLとTBL1の項目は全く同じです。何とかしてTBL1の内容をTBLの最終行に追加したいと思っています。おそらくクエリのSQLビューで"INSERT INTO TBL VALUES(..."と書けばいいんでしょうが、その先にどのように書いていいのかわかりません。ちなみに共通で存在するという項目はkoumokuA、koumokuB、koumokuC、koumokuD、koumokuE、koumokuFとします。どう書けばよいでしょうか?ご返答よろしくお願いします。

  • 指定された趣味を持つメンバーがいるグループを抽出するSQL

    グループテーブル、メンバーテーブル、趣味テーブルの3つのテーブルがあります。 あるメンバーはあるグループに属しており、趣味を1つ持っています。 以下がそのSQLです。 CREATE TABLE groups ( g_id int primary key ); CREATE TABLE members ( m_id int primary key , g_id int , h_id int ); CREATE TABLE hobbies ( h_id int primary key, h_name text ); INSERT INTO groups VALUES ( 1 ); INSERT INTO groups VALUES ( 2 ); INSERT INTO groups VALUES ( 3 ); INSERT INTO members VALUES ( 1, 1, 1); INSERT INTO members VALUES ( 2, 1, 3); INSERT INTO members VALUES ( 3, 1, 4); INSERT INTO members VALUES ( 4, 1, 4); INSERT INTO members VALUES ( 5, 2, 1); INSERT INTO members VALUES ( 6, 2, 2); INSERT INTO members VALUES ( 7, 2, 3); INSERT INTO members VALUES ( 8, 3, 2); INSERT INTO members VALUES ( 9, 3, 3); INSERT INTO members VALUES ( 10, 3, 4); INSERT INTO hobbies VALUES ( 1, 'sports' ); INSERT INTO hobbies VALUES ( 2, 'music' ); INSERT INTO hobbies VALUES ( 3, 'book' ); INSERT INTO hobbies VALUES ( 4, 'drive' ); 指定された趣味を持つメンバーがいるグループを抽出するにはどうすればいいでしょうか? 例えば、「読書が趣味なメンバーとドライブが趣味なメンバーがいるグループは?」「グループ1とグループ3」のような感じです。 一応自分で考えてみたのが、以下ですが、これだと趣味の指定が増減すると大きくSQLが変わってしまいます。 もっといいやり方はないでしょうか? 私はPostgreSQL8を使ってますが、汎用的なSQLであれば、そっちの方がいいです。 SELECT distinct m.g_id FROM members m join hobbies h ON m.h_id = h.h_id where h.h_name = 'drive' INTERSECT SELECT distinct m.g_id FROM members m join hobbies h ON m.h_id = h.h_id where h.h_name = 'book';

  • 複数JOINしているとCOUNTが正しく取得できな

    LAMP環境で開発をしています。 SQL文でCOUNTを求める際に、まとめて結果を求めようとして上手く行きません。 状況としては以下です。 テーブルdはidをkeyにa,b,c3つのテーブルとjoinしています。 id = 1の場合、テーブルa,b,cにマッチするレコードがそれぞれに4個、1個、0個あります。 ひとつひとつを SELECT COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count FROM d INNER JOIN a ON a.id = d.id WHERE d.id = 1 として結果を求めると4,1,0と出るのですが、まとめて SELECT COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count, COUNT(CASE WHEN b.name IS null THEN 1 ELSE null END) as b_count, COUNT(CASE WHEN c.name IS null THEN 1 ELSE null END) as c_count FROM d INNER JOIN a ON a.id = d.id INNER JOIN b ON b.id = d.id INNER JOIN c ON c.id = d.id WHERE d.id = 1 とすると28,5,0という値が返されます。 どのように書けば正しい4,1,0を得られるのでしょうか? よろしくお願いします。

    • ベストアンサー
    • MySQL
  • MySQLから取り出したものを3つに分ける

    MySQLに以下のようなSQLを打ちました。 create table table_list( id int, task varchar(255) ); insert into table_list values(1,'ほげほげ1-1'); insert into table_list values(1,'ほげほげ1-2'); insert into table_list values(2,'ほげほげ2-1); insert into table_list values(2,'ほげほげ2-2'); insert into table_list values(3,'ほげほげ3-1'); そして、空のdiv要素が3つあります。 このデータベースからidの数値別に、div要素へtaskの文字列を入れたいのですが、どうすればいいのでしょうか? SQLで「select * from table_list」をやってからtaskを取り出すのか、3回SQLで「select task from table_list where id=1」のようにするのがよろしいんでしょうか?

    • ベストアンサー
    • PHP
  • 共通のカラムを持つテーブルの外部キーの設定

    MySQLバージョン4.1.16を使用しています。 2つのテーブル「tbl1」と「tbl2」があり、 それぞれ共通のカラムを別のテーブル「common_tbl」に設定(正規化)し そのcommon_idを「tbl1」と「tbl2」が参照します。 例えば、「tbl2」にデータをinsertする場合に、 まず「common_tbl」からinsertして、そのinsert_idを取得(利用)して 「tbl2」にデータを挿入します。 たぶんこの場合のinsertはこのようなやり方だと思います(あまり自信ないなぁ・・・) 「tbl2」の行を削除した場合に、「common_tbl」の行も自動で削除させたいと思うのですが、 どのように外部キーを設定するのか(どこに設定するのか)が分かりません。 「common_tbl」に「tbl1」のtbl1_idと「tbl2」のtbl2_idを それぞれ持たせるのはたぶん違うと思いますし・・・ この場合の正しいテーブル構成を含めてアドバイスをお願いしたいです。 例: 「tbl2」のtbl2_idが2の行を削除したら、 「common_tbl」のcommon_idが3の行も削除したい(外部キーで自動的に) テーブル「tbl1」 +------+---------+----------+ | tbl1_id| tbl1_col1 |common_id | +------+---------+----------+ |  1  |   値1  |   1    | +------+---------+----------+ テーブル「tbl2」 +------+---------+----------+----------+ | tbl2_id| tbl2_col1 | tbl2_col2 |common_id | +------+---------+----------+----------+ |  1  |   値1  |   値1  |    2   | +------+---------+----------+----------+ |  2  |   値2  |   値2  |    3   | +------+---------+----------+----------+ テーブル「common_tbl」 +----------+---------+----------+ |common_id |   col1  |   col2  | +----------+---------+----------+ |  1     |   値1  |   値1  | +----------+---------+----------+ |  2     |   値2  |   値2  | +----------+---------+----------+ |  3     |   値3  |   値3  | +----------+---------+----------+

    • ベストアンサー
    • MySQL
  • 別テーブルのカラムを利用してソートしたい

    別テーブルのカラムを利用してソートしたい MySQLバージョン4.1.16を使用しています。 テーブル「tbl1」をテーブル「tbl2」のcountというカラムを利用して ソートしたいのですが、どのようなSQL文になるのでしょうか? 「tbl2」のidというカラムは外部キーで「tbl1」のidと関係しています。 テーブル「tbl1」 +------+---------+ |   id |  userid  | +------+---------+ |  1  | tanaka  | |  2  | sato   | +------+---------+ テーブル「tbl2」 +------+---------+ |   id |  count  | +------+---------+ |  1  |   10  | |  2  |   3   | +------+---------+ 次のような文かなと思ったのですが、エラーが返ってきます・・・ SELECT * FROM tbl1 ORDER BY (SELECT id FROM tbl2 ORDER BY count)

    • ベストアンサー
    • MySQL