<p>I'm writing a REST API without an ORM and I'm not entirely sure how to implement a partial update. The straightforward approach that I can think of is to grab the entity from the database, update the fields with the decoded JSON (ignoring the empty values), and then hit the database again with all the fields. </p>
<p>I was wondering: can I do this all with SQL/Postgres in one trip?</p>
<p>The SQL would look something like this:</p>
<pre><code>UPDATE users
SET city='New York',
interests=''
WHERE id=1
</code></pre>
<p>But would be modified to ignore the 'interest' column for this specific case since the value is empty. </p>
<p>My database function: </p>
<pre><code>func UpdateUser(db *sql.DB, u models.User) error {
_, err := db.Exec(
`UPDATE users
SET city=$1,
interests=$2
WHERE id=$3`,
u.City, u.Interests u.ID)
return err
}
</code></pre>
<p>But the problem is that <code>u</code> may or may not have city and interest fields (since the user is able to partially update the profile).</p>
<p>EDIT: Fix sql and add context.</p>
<hr/>**评论：**<br/><br/>kardianos: <pre><p>I would highly encurage you to read through the postgresql SQL docs:
<a href="https://www.postgresql.org/docs/current/static/functions-conditional.html" rel="nofollow">https://www.postgresql.org/docs/current/static/functions-conditional.html</a></p>
<p>The answer to your question is to use a CASE statement as follows:</p>
<pre><code>update u set
city = $1,
interests = case
when $2 = '' then u.interests
else $2
end
from
users u
where
u.id = $3;
</code></pre></pre>KeyYam: <pre><p>thanks! this looks promising. I'll try this and read up on the postgresql docs.</p></pre>pcj2bs: <pre><p>You can just do this:</p>
<pre><code>UPDATE users SET city = 'New York' WHERE id = 1
</code></pre></pre>KeyYam: <pre><p>This wouldn't work because the sql is a string (I updated my original post for clarity).</p></pre>dinkumator: <pre><p>You'll have to build up the query yourself. It's not the most efficient, but something like this would work:</p>
<pre><code>func UpdateUser(db *sql.DB, u models.User) error {
cols := []string{}
args := []interface{}{}
if u.City!="" {
args = append(args, u.City)
cols = append(cols, fmt.Sprintf("city=$%d", len(args)))
}
if u.Interests!="" {
args = append(args, u.Interests)
cols = append(cols, fmt.Sprintf("interests=$%d", len(args)))
}
args = append(args, u.ID)
query := "UPDATE users SET "
query = strings.Join(cols, ", ")
query += fmt.Sprintf(" WHERE id=$%d", len(args)))
_, err := db.Exec(query, args...)
return err
}
</code></pre></pre>
