Postgresで別テーブルをJOINしてUPDATEしたい

Question

解決したいこと

GoのMasterminds/squirrelをつかって開発をしています。
Updateをする際に、他のテーブルをJoinしてそこのデータからWhereで条件をつけたいと考えています。
PostgresではUpdateの際のJoinが使えないということで以下のようなSQLをsquirrelで実現したいです。

UPDATE table1 SET column = value
FROM table2
WHERE table1.column2 = table2.column2

また、現在の私のコードはこのような状態です。
Whereの部分を変更して別テーブルの値を条件にしたいと思っています。

SQLと同様の形でquirrelでも書いてみたのですがUnreference等のエラーが出てしまいます。
(↓のコード中ではFromが使用できないため。UpdateBuilderではFromは定義されていない)
何か分かられる方はご教示お願いします。

query, args, err := (sq.StatementBuilder.PlaceholderFormat(sq.Dollar).
		Update(`"user"`).
		SetMap(data).
		Where(sq.Eq{`"user_id"`: 10}).
		ToSql()
	if err != nil {
		return err
	}
	r, err := tx.Exec(query, args...)
	if err != nil {
		return err
	}
	if err := p.exec(r, 1); err != nil {
		return err
	}
	return nil

Answer 1

Masterminds/squirrel のライブラリでシンプルに以下のクエリを生成するのは難しいかと思います。

UPDATE table1 SET column = value
FROM table2
WHERE table1.column2 = table2.column2

なぜなら Update するときのビルダー UpdateBuilder に対して FROM 句を指定できるメソッドがないためです。

https://godoc.org/github.com/Masterminds/squirrel#UpdateBuilder を参照

別の方法として EXISTS 句を用いて絞り込む方法があります。以下は EXISTS を用いて同様の絞り込みができるクエリの例です。EXISTS の条件などはよしなにクエリパラメータで指定できるようにすれば良さそうです。
(まぁでもちょっと複雑なクエリを発行したい場合、個人的には素の SQL を書きたいですね...)

• Masterminds/squirrel を用いたビルダー実装

package main

import (
	"fmt"

	sq "github.com/Masterminds/squirrel"
)

func main() {
	nestedBuilder := sq.StatementBuilder.PlaceholderFormat(sq.Dollar).Select("x").Prefix("EXISTS (").
		From("table1").Join("table2 ON table1.column2 = table2.column2").Suffix(")")

	outerSql, _, _ := sq.StatementBuilder.PlaceholderFormat(sq.Dollar).Update("table1").
		Set("column", "hoge").
		Where(nestedBuilder).ToSql()

	fmt.Println(outerSql)
}

• 出力結果

UPDATE table1 SET column = $1 WHERE EXISTS ( SELECT x FROM table1 JOIN table2 ON
 table1.column2 = table2.column2 )

Comments

[hello_whats_up]

いつもありがとうございます。
おかげさまでに所望の結果を得ることができました！
ただ、あと一つ追加でしたいことがあります。
Updateするカラムの条件として、
Where("table1"."id" = 変数)というものを追加したいのですが、 missing FROM-clause entry for table \"table1\" というエラーに悩まされています。解決策がお分かりでしたら教えていただけると幸いです。

[d_tutuz]

outerの行を以下のように変えればいけると思いますよ

outerSql, _, _ := sq.StatementBuilder.PlaceholderFormat(sq.Dollar).Update("table1").
Set("column", "hoge").
Where(nestedBuilder).Where("table1.id = ?", "111").ToSql()

[d_tutuz]

余談ですが、ライブラリの使い方を知る方法として"テスト"のコードを見てみる、という方法があります。

Masterminds/squirrel にあるテストのコードをみるとライブラリの使い方と、そのライブラリを使用したときに出力できる期待するSQLの例がいろいろ記載されています。(単体テストなので当たり前といえば当たり前ですが)

GoDoc だけではわかりにくいこともあると思うので、実際にライブラリのソースコードをローカルに git clone してテストケースのコードを読んでみるのも、使い方を知る一つの方法だったりします。ご参考までに。

[hello_whats_up]

ありがとうございます！使い方の方法、参考にさせていただきます。

すみません、間違えていました..
Where("table2"."id" = 変数)のようにJoinしたテーブルの値を参照したいときに、上記のようなエラーが出てしまいます。
この場合の対処法をお聞きしたかったです。

[d_tutuz]

EXISTS 句が含まれるサブクエリ側で WHERE 句の絞り込み条件を含めるとどうでしょうか？

nestedBuilder := sq.StatementBuilder.PlaceholderFormat(sq.Dollar).Select("x").Prefix("EXISTS (").
From("table1").Where("table2.id = ?", "111").Join("table1 ON table1.column2 = table2.column2").Suffix(")")

[d_tutuz]

ちなみに ON 句で絞り込む、でも同様に絞り込みが実現できると思います。

[hello_whats_up]

その方法で得たい結果を得ることができました！
本当にありがとうございました！