UPDATE

Seznam témat:

popis funkce

příklad

Popis funkce

Use this statement to modify existing rows in database tables.

UPDATE  [ FIRST  | TOP  n  ] table-list

... SET  set-item , ...

... [ FROM  table-list  ]

... [ WHERE  search-condition  ]

... [ ORDER  BY  expression  [ ASC  | DESC  ], ... ]

UPDATE  table-list

... SET  set-item , ...

... [ VERIFY  ( column-name , ... ) VALUES  ( expression , ... ) ]

... [ WHERE  search-condition  ]

... [ ORDER  BY  expression  [ ASC  | DESC  ], ... ]

UPDATE  table

...PUBLICATION  publication

...{   SUBSCRIBE  BY  expression

   | OLD  SUBSCRIBE  BY  expression

       NEW  SUBSCRIBE  BY  expression

    }

...WHERE  search-condition

set-item:

   column-name  [.field-name ...] = expression

| column-name [.field-name ...].method-name ( [ expression  ] )

Parameters

UPDATE clause    The table is either a base table, a temporary table, or a view. Views can be updated unless the SELECT statement defining the view contains a GROUP BY clause or aggregate function, or involves a UNION operation.

FIRST or TOP clause    Primarily for use with the ORDER BY clause, this clause allows you to update only a certain subset of the rows that satisfy the WHERE clause.

SET clause    If you are updating non-Java columns, the SET clause is of the following form:

SET column-name = expression, ...

Each named column is set to the value of the expression on the right hand side of the equal sign. There are no restrictions on the expression . If the expression is a column-name , the old value is used.

If you are updating Java columns, you can use field-name  to update the value of a public field in the column. Alternatively, you can use a method to set the value. The following clause updates name field of the JProd  column using a method:

SET JProd.setName( 'Tank Top' )

FROM clause    The optional FROM clause allows tables to be updated based on joins. If the FROM clause is present, the WHERE clause qualifies the rows of the FROM clause. Data is updated only in the table list of the UPDATE clause.

If a FROM clause is used, it is important to qualify the table name the same way in both parts of the statement. If a correlation name is used in one place, the same correlation name must be used elsewhere. Otherwise, an error is generated.

This clause is allowed only if ANSI_UPDATE_CONSTRAINTS is set to OFF. See ANSI_UPDATE_CONSTRAINTS option .

 For a full description of joins, see Joins: Retrieving Data from Several Tables .

 For more information, see FROM clause .

WHERE clause     If a WHERE clause is specified, only rows satisfying the search condition are updated. If no WHERE clause is specified, every row is updated.

ORDER BY clause    Normally, the order in which rows are updated does not matter. However, in conjunction with the FIRST or TOP clause the order can be significant.

You must not update columns that appear in the ORDER BY clause unless you set the ANSI_UPDATE_CONSTRAINTS option to OFF. See ANSI_UPDATE_CONSTRAINTS option .

Case sensitivity    Character strings inserted into tables are always stored in the same case as they are entered, regardless of whether the database is case sensitive or not. A CHAR data type column updated with a string Value  is always held in the database with an upper case V and the remainder of the letters lower case. SELECT statements return the string as Value . If the database is not case sensitive, however, all comparisons make Value  the same as value , VALUE , and so on. Further, if a single-column primary key already contains an entry Value , an INSERT of value  is rejected, as it would make the primary key not unique.

Updates that leave a row unchanged    If the new value does not differ from the old value, no change is made to the data. However, BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether or not the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value.

Description

Syntax 1 of the UPDATE statement modifies values in rows of one or more tables. Syntax 2 and 3 are applicable only to SQL Remote.

Syntax 2 is intended for use with SQL Remote only, in single-row updates executed by the Message Agent. The VERIFY clause contains a set of values that are expected to be present in the row being updated. If the values do not match, any RESOLVE UPDATE triggers are fired before the UPDATE proceeds. The UPDATE does not fail simply because the VERIFY clause fails to match.

Syntax 3 of the UPDATE statement is used to implement a specific SQL Remote feature, and is to be used inside a BEFORE trigger.

It provides a full list of SUBSCRIBE BY values any time the list changes. It is placed in SQL Remote triggers so that the database server can compute the current list of SUBSCRIBE BY values. Both lists are placed in the transaction log.

The Message Agent uses the two lists to make sure that the row moves to any remote database that did not have the row and now needs it. The Message Agent also removes the row from any remote database that has the row and no longer needs it. A remote database that has the row and still needs it is not be affected by the UPDATE statement.

For publications created using a subquery in a SUBSCRIBE BY clause, you must write a trigger containing syntax 3 of the UPDATE statement in order to ensure that the rows are kept in their proper subscriptions.

Syntax 3 of the UPDATE statement allows the old SUBSCRIBE BY list and the new SUBSCRIBE BY list to be explicitly specified, which can make SQL Remote triggers more efficient. In the absence of these lists, the database server computes the old SUBSCRIBE BY list from the publication definition. Since the new SUBSCRIBE BY list is commonly only slightly different from the old SUBSCRIBE BY list, the work to compute the old list may be done twice. By specifying both the old and new lists, you can avoid this extra work.

The SUBSCRIBE BY expression is either a value or a subquery.

Syntax 3 of the UPDATE statement makes an entry in the transaction log, but does not change the database table.

Příklad

Příkaz update lze obecně použít k jakémukoli update tabulky nebo změně tabulky .

Např.

update DBA.OMATER set druh_id=substr(normajina,1,4)

Tento update překopíruje první čtyři znaky ze sloupce "normajina" do sloupce "druh_id".

Obecně : update "tabulka" set "do kterého sloupce" = substr ("z kterého sloupce","od ktrého znaku","kolik znaků")

Další příklad

update DBA.OMATER set druh_id='20'

where druh='30'

Zde zamění příkaz u všech materiálů s druhem 30 druh 20 , lze použít např. i ke změně měsíce účetního období u různých dokladů . Je nutné zaměnit všechny tabulky , kde se může položka vyskytovat

Související témata