<HTML ><HEAD ><TITLE >Updating Data</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.73 "><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.3.2 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Data Manipulation" HREF="dml.html"><LINK REL="PREVIOUS" TITLE="Data Manipulation" HREF="dml.html"><LINK REL="NEXT" TITLE="Deleting Data" HREF="dml-delete.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META NAME="creation" CONTENT="2003-02-03T20:17:34"></HEAD ><BODY CLASS="SECT1" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" >PostgreSQL 7.3.2 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="dml.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" >Chapter 3. Data Manipulation</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="dml-delete.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="DML-UPDATE" >3.2. Updating Data</A ></H1 ><P > The modification of data that is already in the database is referred to as updating. You can update individual rows, all the rows in a table, or a subset of all rows. Each column can be updated separately; the other columns are not affected. </P ><P > To perform an update, you need three pieces of information: <P ></P ></P><OL COMPACT="COMPACT" TYPE="1" ><LI ><P >The name of the table and column to update,</P ></LI ><LI ><P >The new value of the column,</P ></LI ><LI ><P >Which row(s) to update.</P ></LI ></OL ><P> </P ><P > Recall from <A HREF="ddl.html" >Chapter 2</A > that SQL does not, in general, provide a unique identifier for rows. Therefore it is not necessarily possible to directly specify which row to update. Instead, you specify which conditions a row must meet in order to be updated. Only if you have a primary key in the table (no matter whether you declared it or not) can you reliably address individual rows, by choosing a condition that matches the primary key. Graphical database access tools rely on this fact to allow you to update rows individually. </P ><P > For example, this command updates all products that have a price of 5 to have a price of 10: </P><PRE CLASS="PROGRAMLISTING" >UPDATE products SET price = 10 WHERE price = 5;</PRE ><P> This may cause zero, one, or many rows to be updated. It is not an error to attempt an update that does not match any rows. </P ><P > Let's look at that command in detail: First is the key word <TT CLASS="LITERAL" >UPDATE</TT > followed by the table name. As usual, the table name may be schema-qualified, otherwise it is looked up in the path. Next is the key word <TT CLASS="LITERAL" >SET</TT > followed by the column name, an equals sign and the new column value. The new column value can be any scalar expression, not just a constant. For example, if you want to raise the price of all products by 10% you could use: </P><PRE CLASS="PROGRAMLISTING" >UPDATE products SET price = price * 1.10;</PRE ><P> As you see, the expression for the new value can also refer to the old value. We also left out the <TT CLASS="LITERAL" >WHERE</TT > clause. If it is omitted, it means that all rows in the table are updated. If it is present, only those rows that match the condition after the <TT CLASS="LITERAL" >WHERE</TT > are updated. Note that the equals sign in the <TT CLASS="LITERAL" >SET</TT > clause is an assignment while the one in the <TT CLASS="LITERAL" >WHERE</TT > clause is a comparison, but this does not create any ambiguity. Of course, the condition does not have to be an equality test. Many other operators are available (see <A HREF="functions.html" >Chapter 6</A >). But the expression needs to evaluate to a Boolean result. </P ><P > You can also update more than one column in an <TT CLASS="LITERAL" >UPDATE</TT > command by listing more than one assignment in the <TT CLASS="LITERAL" >SET</TT > clause. For example: </P><PRE CLASS="PROGRAMLISTING" >UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;</PRE ><P> </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="dml.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="dml-delete.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Data Manipulation</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="dml.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Deleting Data</TD ></TR ></TABLE ></DIV ></BODY ></HTML >