Search code examples
postgresqlgogo-pg

Create Update query by joining two tables with github.com/go-pg/pg

I am trying to update one field in my postgres table by joining some conditions with another table. I referred this link, which give me a query model that relates to my scenario:

UPDATE product as p SET price = 200 FROM  product_segment as ps WHERE p.segment_id = ps.id and p.name = 'diam'

Now I have to convert this query in to orm.Query. I tried with Join() but it doesn't seem to work .

the code I tried :

_, err := c.postgresDB.WithContext(ctx).Model(Product).
    Set("price =?", 200).
    Join("LEFT JOIN product_segment as ps").
    JoinOn("ps.id = product.segment_id").
    Where("name =?", "diam").
    Update()

How do I properly write the code to achieve desired result ???

Solution

  • After many tries, I ended up doing the below code which gave me the result I wanted.

    _, err := c.postgresDB.WithContext(ctx).Model(Product).
    Set("price =? from product_segment as ps", 200).
    Where("name =?", "diam").
    Update()

    Please share if you guys have better methods.