<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.28 2003/11/29 19:51:37 pgsql Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.29 2004/03/03 22:22:24 neilc Exp $ -->

<chapter id="queries">

<title>Queries</title>

<title>The <literal>FROM</literal> Clause</title>

<para>

The <literal>FROM</> clause derives a table from one or more other

tables given in a comma-separated table reference list.

The <xref linkend="sql-from" endterm="sql-from-title"> derives a

table from one or more other tables given in a comma-separated

table reference list.

<synopsis>

FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_reference</replaceable> <optional>, ...</optional></optional>

</synopsis>

</indexterm>

<para>

The syntax of the <literal>WHERE</> clause is

The syntax of the <xref linkend="sql-where"

endterm="sql-where-title"> clause is

<synopsis>

WHERE <replaceable>search_condition</replaceable>

</synopsis>

</synopsis>

<para>

The <literal>GROUP BY</> clause is used to group together those rows in

a table that share the same values in all the columns listed. The

order in which the columns are listed does not matter. The

purpose is to reduce each group of rows sharing common values into

one group row that is representative of all rows in the group.

This is done to eliminate redundancy in the output and/or compute

aggregates that apply to these groups. For instance:

The <xref linkend="sql-groupby" endterm="sql-groupby-title"> is

used to group together those rows in a table that share the same

values in all the columns listed. The order in which the columns

are listed does not matter. The purpose is to reduce each group

of rows sharing common values into one group row that is

representative of all rows in the group. This is done to

eliminate redundancy in the output and/or compute aggregates that

apply to these groups. For instance:

<screen>

<prompt>=></> <userinput>SELECT * FROM test1;</>

x | y

<synopsis>

SELECT DISTINCT <replaceable>select_list</replaceable> ...

</synopsis>

(Instead of <literal>DISTINCT</> the word <literal>ALL</literal>

(Instead of <literal>DISTINCT</> thekeyword <literal>ALL</literal>

can be used to select the default behavior of retaining all rows.)

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.74 2003/12/14 00:15:03 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.75 2004/03/03 22:22:24 neilc Exp $

PostgreSQL documentation

-->

</para>

<para>

<literal>FROM</literal>-clause elements can contain:

The <literal>FROM</literal> clause can contain the following

elements:

<variablelist>

<varlistentry>

A <literal>JOIN</literal> clause combines two

<literal>FROM</> items. Use parentheses if necessary to

determine the order of nesting. In the absence of parentheses,

<literal>JOIN</literal>s nest left-to-right. In any case

<literal>JOIN</literal> binds more tightly than the commas

separating <literal>FROM</> items.

<literal>JOIN</literal>s nest left-to-right. In any case

<literal>JOIN</literal> binds more tightly than the commas

separating <literal>FROM</> items.

</para>

<para>

<literal>CROSS JOIN</> and <literal>INNER JOIN</literal>

produce a simple Cartesian product, the same result as you get from

listing the two items at the top level of <literal>FROM</>,

but restricted by the join condition (if any).

but restricted by the join condition (if any).

<literal>CROSS JOIN</> is equivalent to <literal>INNER JOIN ON

(TRUE)</>, that is, no rows are removed by qualification.

These join types are just a notational convenience, since they

<literal>INTERSECT</>, or <literal>EXCEPT</> clause may only

specify an output column name or number, not an expression.

</para>

<para>

If an <literal>ORDER BY</> expression is a simple name that

matches both a result column name and an input column name,

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.27 2003/11/29 19:51:39 pgsql Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.28 2004/03/03 22:22:24 neilc Exp $

PostgreSQL documentation

-->

<para>

<command>UPDATE</command> changes the values of the specified

columns in all rows that satisfy the condition. Only the columns to

be modified need be mentioned in thestatement; columns not explicitly

<literal>SET</> retain their previous values.

be modified need be mentioned in the<literal>SET</literal> clause;

columns not explicitly modified retain their previous values.

</para>

<para>

clause.

</para>

<para>

There are two ways to modify a table using information contained in

other tables in the database: using sub-selects, or specifying

additional tables in the <literal>FROM</literal> clause. Which

technique is more appropriate depends on the specific

circumstances.

</para>

<para>

You must have the <literal>UPDATE</literal> privilege on the table

to update it, as well as the <literal>SELECT</literal>

<listitem>

<para>

A list of table expressions, allowing columns from other tables

to appear in the <literal>WHERE</> condition and the update expressions.

to appear in the <literal>WHERE</> condition and the update

expressions. This is similar to the list of tables that can be

specified in the <xref linkend="sql-from"

endterm="sql-from-title"> of a <command>SELECT</command>

statement; for example, an alias for the table name can be

specified.

</para>

</listitem>

</varlistentry>

<para>

Change the word <literal>Drama</> to <literal>Dramatic</> in the

column <structfield>kind</> of the table <literal>films</literal>:

column <structfield>kind</> of the table <structname>films</structname>:

<programlisting>

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

<para>

Adjust temperature entries and reset precipitation to its default

value in one row of the table <literal>weather</literal>:

value in one row of the table <structname>weather</structname>:

<programlisting>

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT

WHERE city = 'San Francisco' AND date = '2003-07-03';

</programlisting>

</para>

<para>

Increment the sales count of the salesperson who manages the

account for Acme Corporation, using the <literal>FROM</literal>

clause syntax:

<programlisting>

UPDATE employees SET sales_count = sales_count + 1 FROM accounts

WHERE accounts.name = 'Acme Corporation'

AND employees.id = accounts.sales_person;

</programlisting>

Perform the same operation, using a sub-select in the

<literal>WHERE</literal> clause:

<programlisting>

UPDATE employees SET sales_count = sales_count + 1 WHERE id =

(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

</programlisting>

</para>

</refsect1>

<refsect1>

<title>Compatibility</title>

<para>

This command conforms to the SQL standard. The

This command conforms to the<acronym>SQL</acronym> standard. The

<literal>FROM</literal> clause is a

<productname>PostgreSQL</productname> extension.

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.34 2004/01/22 19:50:21 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/trigger.sgml,v 1.35 2004/03/03 22:22:24 neilc Exp $

-->

<chapter id="triggers">

<para>

The function <function>trigf</> reports the number of rows in the

table <literal>ttest</> and skips the actual operation if the

table <structname>ttest</> and skips the actual operation if the

command attempts to insert a null value into the column

<literal>x</>. (So the trigger acts as a not-null constraint but

<structfield>x</>. (So the trigger acts as a not-null constraint but

doesn't abort the transaction.)

</para>