UPDATE (original) (raw)

UPDATE changes the values of the specified columns in all rows that satisfy the condition. More about UPDATE statement can be found at:
PostgreSQL - https://www.postgresql.org/docs/11/sql-update.html
MySQL - https://dev.mysql.com/doc/refman/8.0/en/update.html
MariaDB - https://mariadb.com/kb/en/library/update/

Following clauses are supported:

• UPDATE(columns...) - list of columns to update
• SET(values...) - list of values for columns
• MODEL(model) - list of values for columns will be extracted from model object
• WHERE(condition) - only rows for which condition returns true will be updated.
• FROM(tables...) - table expression allowing columns from other tables to appear in the WHERE condition and update expressions. (postgres, sqlite)
• RETURNING(output_expression...) - expressions to be computed and returned by the UPDATE statement after each row is updated. Expressions can use any column names of the table. (postgres, sqlite)

Update using SET method (not recommended, see bellow)

// replace all Bing links with Yahoo updateStmt := Link.UPDATE(Link.Name, Link.URL). SET("Yahoo", "http://yahoo.com"). WHERE(Link.Name.EQ(String("Bing")))

// OR using type-safe SET updateStmt := Link.UPDATE(). SET( Link.Name.SET(String("Yahoo")), Link.URL.SET(String("http://yahoo.com")), ). WHERE(Link.Name.EQ(String("Bing")))

Debug sql of above statement:

UPDATE test_sample.link -- 'test_sample' is name of the schema SET (name, url) = ('Yahoo', 'http://yahoo.com') WHERE link.name = 'Bing';

Update using MODEL (recommended)

This notation is recommended, because model types will add type and pointer safety to update query.

yahoo := model.Link{ URL: "http://www.yahoo.com", Name: "Yahoo", }

updateStmt := Link. UPDATE(Link.Name, Link.URL, Link.Description). MODEL(yahoo). WHERE(Link.Name.EQ(String("Bing")))

Link.Name, Link.URL, Link.Description - can be replaced with `Link.MutableColumns(all columns minus primary key column). Primary key columns usually are not updated.

updateStmt := Link. UPDATE(Link.MutableColumns). MODEL(yahoo). WHERE(Link.Name.EQ(String("Bing")))

ColumnList can be used to pass a custom list of columns to the UPDATE query:

columnList := ColumnList{Link.Name, Link.Description} updateStmt := Link. UPDATE(columnList). MODEL(yahoo). WHERE(Link.Name.EQ(String("Bing")))

UPDATE with JOIN

Postgres/CockroachDB/Sqlite

stmt := Staff.UPDATE(Staff.LastName). SET(String("Paul")). FROM(Address). WHERE(AND( Address.AddressID.EQ(Staff.AddressID), Staff.StaffID.EQ(Int(1), Address.City.NOT_EQ("London"), ))

MySQL/MariaDB

stmt := Staff.INNER_JOIN(Address, Address.AddressID.EQ(Staff.AddressID)). UPDATE(Staff.LastName). SET(String("Paul")). WHERE(Staff.StaffID.EQ(Int(1)).AND(Address.City.NOT_EQ("London")))

Execute statement

To execute update statement and get sql.Result:

res, err := updateStmt.Exec(db)

To execute PostgreSQL update statement and return row records updated, statement has to have RETURNING clause:

updateStmt := Link. UPDATE(Link.MutableColumns). MODEL(yahoo). WHERE(Link.Name.EQ(String("Bing"))). RETURNING(Link.AllColumns)

dest := []model.Link{}

err := updateStmt.Query(db, &dest)

Use ExecContext and QueryContext to provide context object to execution.

SQL table used for the example:

CREATE TABLE IF NOT EXISTS link ( id serial PRIMARY KEY, url VARCHAR (255) NOT NULL, name VARCHAR (255) NOT NULL, description VARCHAR (255) );