How to do subquery on where statement with Gorm?-CodePudding

I got a little problem with gorm when I need to do subquery inside where statement. I can do this easily with Raw method, but I want to use gorm's method so my function can do this update dynamically. I have query for update like this

    UPDATE table_A 
    SET
        col_A = ?,
        col_B = ?
    WHERE col_C = 
    (
        SELECT col_C from table_B 
        WHERE col_D = ?
    )

And here is my Query using newest Gorm where data inside Updates method is a struct of table A

tx.Table("table_A").
Where("col_C", tx.Table("table_B").Select("col_C").Where("col_D", value_D)).
Updates(data)

And this is the result, this result becomes an error because SELECT on where statement as subquery doesn't written inside parenthesis

UPDATE `table_A` SET `col_a`='col_A',`col_b`='col_B' WHERE col_C = SELECT col_C FROM `table_B` WHERE col_D = 'test123'

is there a way to solve my problem using gorm? or currently gorm still does not support it?

CodePudding user response：

Based in the gorm documentations, if you pass an instance of *gorm.DB, gorm will be able to perform the subquery, but in my case it didn't :). So what I did was I manually surrounded the subquery in parenthesis, something like this in your case:

subquery := tx.Table("table_B").Select("col_C").Where("col_D", value_D)

tx.Table("table_A").
    Where("col_C = (?)", subquery).
    Updates(data)