Hello, I'm trying to create a "Create or Update" approach on MSSQL using Gorm.

Since GORM has firstOrCreate() and Assign() I would like to use it, but there is a problem. In MSSQL it adds OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY at the end of the query that returns the following error:

Invalid usage of the option NEXT in the FETCH statement.

Reading other issues about this problem, I tried some solution including adding and OrderBy option (.Order("ID DESC")) and with this, it works fine unless I have to update it.

I would like to use Assign in order to update it and now, usign the OrderBy option, I have another error:

mssql: Incorrect syntax near the keyword 'ORDER'.

of course because it puts ORDER BY ID DESC after the UPDATE command.

I tried to create a similar code in the field below and I put the SQL that GORM generates for me.

How could I do it? Is there another safer way that I don't know?

What version of Go are you using (go version)?

1.13.6

Which database and its version are you using?

MSSQL 2017

Please provide a complete runnable program to reproduce your issue. IMPORTANT

package main

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mssql"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    _ "github.com/jinzhu/gorm/dialects/postgres"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

var db *gorm.DB

func init() {
    var err error
    db, err = gorm.Open("mssql", "sqlserver://gorm:LoremIpsum86@localhost:9930?database=gorm")
    if err != nil {
        panic(err)
    }
    db.LogMode(true)
}

func main() {
      var myUser User

     myUser.Name = "Jinzhu"
    myUser.Age = 18

    db.Debug().Where(User{Name: myUser.Name}).Order("ID DESC").Assign(User{UpdatedAt: time.Now()}).FirstOrCreate(&myUser).Error
}

Generated SQL

First Or Create

SELECT * FROM ******* WHERE ******* OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Assign

UPDATE ******** SET ******** WHERE ******** ORDER BY ID DESC

Comment From: github-actions[bot]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog

Comment From: liniu

V2 也是同样的报这个错

Comment From: endlesstravel

same issue here Gorm Problem with MSSQL: code is Gorm Problem with MSSQL: 

gorm.io/gorm v1.23.1 
gorm.io/driver/sqlserver v1.3.2