4
4
<title>6.2.�Updating Data</title>
5
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.64.1">
8
<link rel="home" href="index.html" title="PostgreSQL 8.0.0beta5 Documentation">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
8
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
9
9
<link rel="up" href="dml.html" title="Chapter�6.�Data Manipulation">
10
<link rel="previous" href="dml.html" title="Chapter�6.�Data Manipulation">
10
<link rel="prev" href="dml.html" title="Chapter�6.�Data Manipulation">
11
11
<link rel="next" href="dml-delete.html" title="6.3.�Deleting Data">
12
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
13
14
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
14
<div class="titlepage">
15
<div><div><h2 class="title" style="clear: both">
16
<a name="dml-update"></a>6.2.�Updating Data</h2></div></div>
19
<a name="id2527182"></a><a name="id2527192"></a><p> The modification of data that is already in the database is
15
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
16
<a name="dml-update"></a>6.2.�Updating Data</h2></div></div></div>
17
<a name="id579701"></a><a name="id579712"></a><p> The modification of data that is already in the database is
20
18
referred to as updating. You can update individual rows, all the
21
19
rows in a table, or a subset of all rows. Each column can be
22
20
updated separately; the other columns are not affected.
48
46
This may cause zero, one, or many rows to be updated. It is not
49
47
an error to attempt an update that does not match any rows.
51
<p> Let's look at that command in detail: First is the key word
52
<tt class="literal">UPDATE</tt> followed by the table name. As usual,
49
<p> Let's look at that command in detail. First is the key word
50
<code class="literal">UPDATE</code> followed by the table name. As usual,
53
51
the table name may be schema-qualified, otherwise it is looked up
54
in the path. Next is the key word <tt class="literal">SET</tt> followed
52
in the path. Next is the key word <code class="literal">SET</code> followed
55
53
by the column name, an equals sign and the new column value. The
56
54
new column value can be any scalar expression, not just a constant.
57
55
For example, if you want to raise the price of all products by 10%
60
58
<pre class="programlisting">UPDATE products SET price = price * 1.10;</pre>
62
As you see, the expression for the new value can also refer to the
63
old value. We also left out the <tt class="literal">WHERE</tt> clause.
60
As you see, the expression for the new value can refer to the existing
61
value(s) in the row. We also left out the <code class="literal">WHERE</code> clause.
64
62
If it is omitted, it means that all rows in the table are updated.
65
If it is present, only those rows that match the condition after
66
the <tt class="literal">WHERE</tt> are updated. Note that the equals
67
sign in the <tt class="literal">SET</tt> clause is an assignment while
68
the one in the <tt class="literal">WHERE</tt> clause is a comparison, but
69
this does not create any ambiguity. Of course, the condition does
63
If it is present, only those rows that match the
64
<code class="literal">WHERE</code> condition are updated. Note that the equals
65
sign in the <code class="literal">SET</code> clause is an assignment while
66
the one in the <code class="literal">WHERE</code> clause is a comparison, but
67
this does not create any ambiguity. Of course, the
68
<code class="literal">WHERE</code> condition does
70
69
not have to be an equality test. Many other operators are
71
70
available (see <a href="functions.html" title="Chapter�9.�Functions and Operators">Chapter�9, <i>Functions and Operators</i></a>). But the expression
72
71
needs to evaluate to a Boolean result.
74
<p> You can also update more than one column in an
75
<tt class="literal">UPDATE</tt> command by listing more than one
76
assignment in the <tt class="literal">SET</tt> clause. For example:
73
<p> You can update more than one column in an
74
<code class="command">UPDATE</code> command by listing more than one
75
assignment in the <code class="literal">SET</code> clause. For example:
78
77
<pre class="programlisting">UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;</pre>