File: sql-update.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (352 lines) | stat: -rw-r--r-- 5,012 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>UPDATE</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="UNLISTEN"
HREF="sql-unlisten.html"><LINK
REL="NEXT"
TITLE="VACUUM"
HREF="sql-vacuum-1.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-unlisten.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-vacuum-1.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>UPDATE</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>UPDATE &#8212; Replaces values of columns in a table</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>UPDATE <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> SET <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [, ...]
    [ FROM <TT
CLASS="REPLACEABLE"
><I
>fromlist</I
></TT
> ]
    [ WHERE <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
> ]</PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></DT
><DD
><P
>          The name of an existing table.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
></DT
><DD
><P
>The name of a column in <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
></DT
><DD
><P
>          A valid expression or value to assign to column.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>fromlist</I
></TT
></DT
><DD
><P
>          A <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
 non-standard extension to allow columns
          from other tables to appear in the WHERE condition.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
></DT
><DD
><P
>          Refer to the SELECT statement for a further description
          of the WHERE clause.</P
></DD
></DL
><P></P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
>UPDATE <TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
></DT
><DD
><P
>          Message returned if successful.
The <TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
>
          means the number of rows updated.
If <TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
>
 is equal 0 no rows are updated.</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
>   UPDATE changes the values of the columns specified for
   all rows which satisfy condition. Only the columns 
   to be modified need appear as column.&#13;</P
><P
>   Array references use the same syntax found in SELECT.
   That is, either single array elements, a range of array
   elements or the entire array may be replaced with a single
   query.&#13;</P
><P
>   You must have write access to the table in order to modify
   it, as well as read access to any table whose values are
   mentioned in the WHERE condition.
 </P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
></P
><PRE
CLASS="PROGRAMLISTING"
>   --Change word "Drama" with "Dramatic" on column kind:
   --
   UPDATE films 
      SET kind = 'Dramatic'
      WHERE kind = 'Drama';

   SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama';

   code |title        |did| date_prod|kind      |len
   -----+-------------+---+----------+----------+------
   BL101|The Third Man|101|1949-12-23|Dramatic  | 01:44
   P_302|Becket       |103|1964-02-03|Dramatic  | 02:28
   M_401|War and Peace|104|1967-02-12|Dramatic  | 05:57
   T_601|Yojimbo      |106|1961-06-16|Dramatic  | 01:50
   DA101|Das Boot     |110|1981-11-11|Dramatic  | 02:29</PRE
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><P
></P
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
>   SQL92 defines a different syntax for positioned UPDATE statement:

<PRE
CLASS="PROGRAMLISTING"
>   UPDATE table SET column = expression [, ...]
          WHERE CURRENT OF <TT
CLASS="REPLACEABLE"
><I
>cursor</I
></TT
></PRE
>

   where <TT
CLASS="REPLACEABLE"
><I
>cursor</I
></TT
>
 identifies an open cursor.</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-unlisten.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-vacuum-1.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>UNLISTEN</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>VACUUM</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>