Building dynamic update with SET?

[yuphing-ong]

An UPDATE statement is of the form:

stmt := TestTable.UPDATE().
    SET(
        TestTable.TestCol1.SET(sql.Int(1)),
    )

But how would I add to this SET statement dynamically?

I tried:

stmt = stmt.SET(TestTableTestCol2.SET(sql.Int(2)),)

but this seems to overwrite the previous SET statement, i.e query, args := stmt.Sql() shows only 1 set statement (the latest) instead of 2

I also tried:

setList := [...]ColumnAssignment{}
setList = append(setList, TestTable.TestCol1.SET(sql.Int1))

stmt := TestTable.UPDATE().
    SET(setList)

But I get:

panic: jet: mismatch in numbers of columns and values for SET clause [recovered]
        panic: jet: mismatch in numbers of columns and values for SET clause

FYI this is using the sqlite driver. I haven't tried with any other database.

[yuphing-ong]

I did some further testing, to see if I could combine using a model with a set statement, but when I do so, there's a comma missing in the generated set statement, like this:

columnList := ColumnList{TestTable.TestColumn1}
modelValues := model.TestTable{TestColumn1:1}
stmt := TestTable.UPDATE(columnList).
    MODEL(modelValues).
    SET(TestTableTestCol2.SET(sql.Int(2)),).
    WHERE(...)

And it generated this:

UPDATE test_table
SET test_column1 = ? <--- missing ","
SET test_column2 = ?
WHERE id = 
...
                Error Trace:    test.go:99
                Error:          Received unexpected error:
                                jet: near "SET": syntax error

[houten11]

You either use MODEL or SET, but not both - https://github.com/go-jet/jet/wiki/UPDATE.

[yuphing-ong]

I found a workaround:

var setList[]interface{} //I tried to use ColumnAssignment, but it only accepts interface{}
setList = append(setList, TestTable.TestColumn1.SET(sql.Int(1)))
setList = append(setList, TestTable.TestColumn2.SET(sql.Int(2)))
stmt := TestTable.UPDATE().
	SET(
		setList[0],
		setList[1:]...,
	)

Would appreciate if anyone has any feedback on a better way of doing this

[houten11]

This will work, but it will panic if setList has only one element.

[houten11]

The recommended way is to use ColumnList and MODEL:

columnList := ColumnList{TestTable.TestColumn1}
modelValue := model.TestTable{TestColumn1:1}
stmt := TestTable.UPDATE(columnList).
    MODEL(modelValue).
    WHERE(...)

columnList selects which columns to update.

[yuphing-ong]

can MODEL incorporate SQL? My example was simplified, but actually I want to update the fields atomically e.g.
SET(TestTableTestCol2.SET(IntExp(SELECT(TestTable.TestCol2.ADD(Int(2)))),).

[houten11]

In that case, you'll have to use your workaround.
Also, there is no need for SELECT in your column assignment:

SET(TestTable.TestCol2.SET(TestTable.TestCol2.ADD(Int(2))))