update.sgml 5.35 KB
Newer Older
1
<!--
2
$Header: /cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v 1.7 2000/01/29 16:58:27 petere Exp $
3 4 5
Postgres documentation
-->

6 7
<refentry id="SQL-UPDATE">
 <refmeta>
8
  <refentrytitle id="SQL-UPDATE-TITLE">
9 10 11 12 13 14 15 16 17 18 19 20 21 22
   UPDATE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   UPDATE
  </refname>
  <refpurpose>
   Replaces values of columns in a table
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
23
   <date>1999-07-20</date>
24 25
  </refsynopsisdivinfo>
  <synopsis>
26
UPDATE <replaceable class="PARAMETER">table</replaceable> SET <replaceable class="PARAMETER">col</replaceable> = <replaceable class="PARAMETER">expression</replaceable> [, ...]
27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
    [ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
    [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
  </synopsis>

  <refsect2 id="R2-SQL-UPDATE-1">
   <refsect2info>
    <date>1998-09-24</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">table</replaceable></term>
      <listitem>
       <para>
	The name of an existing table.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">column</replaceable></term>
      <listitem>
       <para>
	The name of a column in <replaceable class="PARAMETER">table</replaceable>.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">expression</replaceable></term>
      <listitem>
       <para>
	A valid expression or value to assign to column.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">fromlist</replaceable></term>
      <listitem>
       <para>
	A <productname>Postgres</productname>
	non-standard extension to allow columns
	from other tables to appear in the WHERE condition.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">condition</replaceable></term>
      <listitem>
       <para>
	Refer to the SELECT statement for a further description
	of the WHERE clause.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-UPDATE-2">
   <refsect2info>
    <date>1998-09-24</date>
   </refsect2info>
   <title>
    Outputs
   </title>

   <para>
    <variablelist>
     <varlistentry>
      <term><computeroutput>
UPDATE <replaceable class="parameter">#</replaceable>
      </computeroutput></term>
      <listitem>
       <para>
	Message returned if successful.
	The <replaceable class="parameter">#</replaceable>
	means the number of rows updated.
	If <replaceable class="parameter">#</replaceable>
	is equal 0 no rows are updated.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-UPDATE-1">
  <refsect1info>
   <date>1998-09-24</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <command>UPDATE</command> changes the values of the columns specified for
   all rows which satisfy condition. Only the columns 
127
   to be modified need appear as columns in the statement.
128 129 130
  </para>

  <para>
131 132
   Array references use the same syntax found in
   <xref linkend="sql-select-title" endterm="sql-select-title">.
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
   That is, either single array elements, a range of array
   elements or the entire array may be replaced with a single
   query.
  </para>

  <para>
   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.
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-UPDATE-2">
  <title>
   Usage
  </title>

  <para>
   Change word "Drama" with "Dramatic" on column kind:

   <programlisting>
UPDATE films 
    SET kind = 'Dramatic'
    WHERE kind = 'Drama';
SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama';
158 159 160 161 162 163 164 165

   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
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182
   </programlisting>
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-UPDATE-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-UPDATE-4">
   <refsect2info>
    <date>1998-09-24</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
183 184
    <acronym>SQL92</acronym> defines a different syntax for
    the positioned UPDATE statement:
185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213

    <synopsis>
UPDATE table SET column = expression [, ...]
    WHERE CURRENT OF <replaceable class="parameter">cursor</replaceable>
    </synopsis>

    where <replaceable class="parameter">cursor</replaceable>
    identifies an open cursor.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->