Query Building: UPDATE

Contents

• Basic example
• UPDATE with join
• Multi-table UPDATE (MySQL only)
• Clause manipulation

UPDATE public.users
SET name = 'bob', password = '1234'
WHERE email = 'bob@email.com';

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 */
}

--------------
-- 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;

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 */
}

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.

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;

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 */
}

• WITH   clauses
  For more information see Common Table Expressions.

  var q sq.InsertQuery
  
  // Adding
  
  q = q.With(cte1, cte2, cte3)
  // is equivalent to //
  q = q.With(cte1).With(cte2).With(cte3)
  // is equivalent to //
  q.CTEs = []sq.CTE{cte1, cte2, cte3}
  
  // Removing
  
  q.CTEs = nil
  

• UPDATE   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.Update(table)
  // is equivalent to //
  q.UpdateTable = table
  
  // Removing
  
  q.UpdateTable = nil
  

• SET   clauses

  var q sq.InsertQuery
  
  // Adding
  
  q = q.Set(assignment1, assignment2)
  // is equivalent to //
  q.Assignments = []sq.Assignment{assignment1, assignment2}
  
  // Removing
  
  q.Assignments = nil
  

• FROM (postgres only)   clauses

  var q sq.UpdateQuery
  
  // Adding
  
  q = q.From(table)
  // is equivalent to //
  q.FromTable = table
  
  // Removing
  
  q.FromTable = nil
  

• JOIN   clauses

  var q sq.UpdateQuery
  
  // Adding
  
  q = q.Join(table1, predicate1).
      LeftJoin(table2, predicate2).
      FullJoin(table3, predicate3)
  // is equivalent to //
  q.JoinTables = []sq.JoinTable{
      sq.Join(table1, predicate1),
      sq.LeftJoin(table2, predicate2),
      sq.FullJoin(table3, predicate3),
  }
  
  // Removing
  
  q.JoinTables = nil
  

• WHERE   clauses

  var q sq.UpdateQuery
  
  // Adding
  
  q = q.Where(predicate1, predicate2, predicate3)
  // is equivalent to //
  q = q.Where(predicate1).Where(predicate2).Where(predicate3)
  // is equivalent to //
  q.WherePredicate.Predicates = []sq.Predicate{
      predicate1, 
      predicate2, 
      predicate3,
  }
  
  // Removing
  
  q.WherePredicate.Predicates = nil
  

• ORDER BY (mysql only)   clauses

  var q sq.UpdateQuery
  
  // Adding
  
  q = q.OrderBy(field1, field2, field3)
  // is equivalent to //
  q = q.OrderBy(field1).OrderBy(field2).OrderBy(field3)
  // is equivalent to //
  q.OrderByFields = []sq.Field{field1, field2, field3}
  
  // Removing
  
  q.OrderByFields = nil
  

• LIMIT (mysql only)   clauses

  var q sq.UpdateQuery
  
  // Adding
  
  q = q.Limit(10)
  // is equivalent to //
  limit := int64(10)
  q.LimitValue = &limit
  
  // Removing
  
  q.LimitValue = nil
  

• RETURNING (postgres only)   clauses

  var q sq.UpdateQuery
  
  // Adding
  
  q = q.Returning(field1, field2, field3)
  // is equivalent to //
  q = q.Returning(field1).Returning(field2).Returning(field3)
  // is equivalent to //
  q.ReturningFields = []sq.Field{field1, field2, field3}
  
  // Removing
  
  q.ReturningFields = nil