1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Data Manipulation"
18
TITLE="Inserting Data"
19
HREF="dml-insert.html"><LINK
22
HREF="dml-delete.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Inserting Data"
57
HREF="dml-insert.html"
66
TITLE="Data Manipulation"
74
>Chapter 6. Data Manipulation</TD
80
TITLE="Data Manipulation"
90
HREF="dml-delete.html"
105
>6.2. Updating Data</A
108
> The modification of data that is already in the database is
109
referred to as updating. You can update individual rows, all the
110
rows in a table, or a subset of all rows. Each column can be
111
updated separately; the other columns are not affected.
114
> To update existing rows, use the <A
115
HREF="sql-update.html"
118
command. This requires
119
three pieces of information:
127
>The name of the table and column to update</P
131
>The new value of the column</P
135
>Which row(s) to update</P
144
> that SQL does not, in general,
145
provide a unique identifier for rows. Therefore it is not
146
always possible to directly specify which row to update.
147
Instead, you specify which conditions a row must meet in order to
148
be updated. Only if you have a primary key in the table (independent of
149
whether you declared it or not) can you reliably address individual rows
150
by choosing a condition that matches the primary key.
151
Graphical database access tools rely on this fact to allow you to
152
update rows individually.
155
> For example, this command updates all products that have a price of
156
5 to have a price of 10:
158
CLASS="PROGRAMLISTING"
159
>UPDATE products SET price = 10 WHERE price = 5;</PRE
161
This might cause zero, one, or many rows to be updated. It is not
162
an error to attempt an update that does not match any rows.
165
> Let's look at that command in detail. First is the key word
169
> followed by the table name. As usual,
170
the table name can be schema-qualified, otherwise it is looked up
171
in the path. Next is the key word <TT
175
by the column name, an equal sign, and the new column value. The
176
new column value can be any scalar expression, not just a constant.
177
For example, if you want to raise the price of all products by 10%
180
CLASS="PROGRAMLISTING"
181
>UPDATE products SET price = price * 1.10;</PRE
183
As you see, the expression for the new value can refer to the existing
184
value(s) in the row. We also left out the <TT
188
If it is omitted, it means that all rows in the table are updated.
189
If it is present, only those rows that match the
193
> condition are updated. Note that the equals
197
> clause is an assignment while
201
> clause is a comparison, but
202
this does not create any ambiguity. Of course, the
207
not have to be an equality test. Many other operators are
209
HREF="functions.html"
211
>). But the expression
212
needs to evaluate to a Boolean result.
215
> You can update more than one column in an
219
> command by listing more than one
220
assignment in the <TT
223
> clause. For example:
225
CLASS="PROGRAMLISTING"
226
>UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;</PRE
235
SUMMARY="Footer navigation table"
246
HREF="dml-insert.html"
264
HREF="dml-delete.html"
b'\\ No newline at end of file'