< Prev: INSERT
Up: Query Building
Next: DELETE >

Query Building: UPDATE

Contents

Basic example

  contents
SQL
UPDATE public.users
SET name = 'bob', password = '1234'
WHERE email = 'bob@email.com';
Go
u := tables.USERS()

// ...if you use ToSQL
query, args := sq.Update(u).
    Set(
        u.NAME.Set("bob"),
        u.PASSWORD.Set("1234"),
    ).
    Where(u.EMAIL.EqString("bob@email.com")).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.Update(u).
    Set(
        u.NAME.Set("bob"),
        u.PASSWORD.Set("1234"),
    ).
    Where(u.EMAIL.EqString("bob@email.com")).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

UPDATE with join

  contents
SQL
--------------
-- Postgres --
--------------

UPDATE public.users AS u
SET name = 'bob', password = '1234'
FROM public.user_roles AS ur
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
WHERE u.user_id = ur.user_id AND urs.team_id = 15;

-----------
-- MySQL --
-----------

UPDATE public.users AS u
JOIN public.user_roles AS ur ON ur.user_id = u.user_id
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
SET u.name = 'bob', u.password = '1234'
WHERE urs.team_id = 15;
Go
u := tables.USERS().As("u")
ur := tables.USER_ROLES().As("ur")
urs := tables.USER_ROLES_STUDENTS().As("urs")

//----------//
// Postgres //
//----------//

// ...if you use ToSQL
query, args := sq.Update(u).
    Set(u.NAME.SetString("bob"), u.PASSWORD.SetString("1234")).
    From(ur).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(u.USER_ID.Eq(ur.USER_ID), urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.Update(u).
    Set(u.NAME.SetString("bob"), u.PASSWORD.SetString("1234")).
    From(ur).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(u.USER_ID.Eq(ur.USER_ID), urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

//-------//
// MySQL //
//-------//

// ...if you use ToSQL
query, args := sq.Update(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Set(u.NAME.SetString("bob"), u.PASSWORD.SetString("1234")).
    Where(urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.Update(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Set(u.NAME.SetString("bob"), u.PASSWORD.SetString("1234")).
    Where(urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

Multi-table UPDATE

  contents

Note: Multi-table UPDATE is a mysql-only feature.

It is a natural extension of an UPDATE with join, simply set values for the additional tables that you have already joined. (Postgres allows for joining other tables in UPDATE, but does not allow you to set values for them.)

You will not be able to use the ORDER BY and LIMIT clauses in a multi-table UPDATE.

SQL
UPDATE public.users AS u
JOIN public.user_roles AS ur ON ur.user_id = u.user_id
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
SET u.name = 'bob', ur.role = 'student', urs.team_id = 3
WHERE urs.team_id = 15;
Go
u := tables.USERS()

// ...if you use ToSQL
query, args := sq.Update(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Set(
        u.NAME.SetString("bob"), 
        ur.ROLE.SetString("student"),
        urs.TEAM_ID.SetInt(3),
    ).
    Where(urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.Update(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Set(
        u.NAME.SetString("bob"), 
        ur.ROLE.SetString("student"),
        urs.TEAM_ID.SetInt(3),
    ).
    Set(u.NAME.SetString("bob"), u.PASSWORD.SetString("1234")).
    Where(urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

Clause manipulation

  contents
Clauses are just struct fields, you can either use method chaining to add clauses or manipulate the struct fields directly.
Clauses: WITH, UPDATE, SET, FROM (postgres only), JOIN, WHERE, ORDER BY (mysql only), LIMIT (mysql only), RETURNING (postgres only)
< Prev: INSERT
Up: Query Building
Next: DELETE >