Oh wow, thanks Karl. I will remember that for the future
a shame you werent there yesterday before i set that field by hand. Er.. I
did look up the update statement in the LangRef Guide...
still, although trying to do it via SQL took me 3 hours, it only took 20
minutes to do it manually
UPDATE
Changes the data in all or part of an existing row in a table, view, or
active set of a cursor.
Available in SQL, DSQL, and isql.
Syntax SQL form:
UPDATE [TRANSACTION transaction] {table | view}
SET col = < val> [, col = <val> ]
[WHERE <search_condition> | WHERE CURRENT OF cursor];
DSQL and isql form:
UPDATE { table | view}
SET col = < val> [, col = <val> ]
[WHERE <search_condition>
<val> = {
col [<array_dim>] | : variable
| <constant> | <expr> | <function>
| udf ([ <val> [, <val> ]])
| NULL | USER | ?}
[COLLATE collation]
<array_dim> = [[x:]y [, [x:]y ]]
<constant> = num | ' string' | charsetname ' string'
<expr> = A valid SQL expression that results in a single value.
<function> = {
CAST ( <val> AS <datatype>)
| UPPER ( <val>)
| GEN_ID ( generator, <val>)}
<search_condition> = See CREATE TABLE for a full description.
Notes on the UPDATE statement
g
In SQL and isql, you cannot use val as a parameter placeholder (like "?").
g
In DSQL and isql, val cannot be a variable.
g
You cannot specify a COLLATE clause for Blob columns.
Description UPDATE modifies one or more existing rows in a table or view.
UPDATE is one of the
database privileges controlled by GRANT and REVOKE.
For searched updates, the optional WHERE clause can be used to restrict
updates to a
subset of rows in the table. Searched updates cannot update array slices.
IMPORTANT Without a WHERE clause, a searched update modifies all rows in a
table.
When performing a positioned update with a cursor, the WHERE CURRENT OF
clause must
be specified to update one row at a time in the active set.
Note When updating a Blob column, UPDATE replaces the entire Blob with a new
value.
Examples The following isql statement modifies a column for all rows in a
table:
UPDATE CITIES
SET POPULATION = POPULATION * 1.03;
The next embedded SQL statement uses a WHERE clause to restrict column
modification
to a subset of rows:
EXEC SQL
UPDATE PROJECT
SET PROJ_DESC = :blob_id
WHERE PROJ_ID = :proj_id;
See Also DELETE, GRANT, INSERT, REVOKE, SELECT
Argument Description
TRANSACTION
transaction
Name of the transaction under control of which the statement is
executed
table | view Name of an existing table or view to update.
SET col = val Specifies the columns to change and the values to assign to
those
columns
WHERE
search_condition
Searched update only; specifies the conditions a row must meet to be
modified
WHERE CURRENT OF cursor Positioned update only;specifies that thecurrent
rowofacursors active
set is to be modified
Not available in DSQL and isql
----- Original Message -----
From: "Karl Reynolds" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Thursday, August 07, 2003 12:45 AM
Subject: RE: [DUG]: joining in update query
> Of course you can do this in Interbase. OTTOMH (I'm not at work right
now):
>
> update TABLE1 t1
> set updatefield = ((
> select t3.updatefield
> from TABLE2 t2, TABLE3 t3
> Where t1.idfield = t2.idfield
> and t2.anotheridfield = t3.anotheridfield))
>
> And you can add "where <t1 condition>" on the bottom if you need to.
Note:
> TWO pairs of parentheses. This is all documented in the Interbase
Language
> Reference guide. Look up the Update statement.
>
> Cheers,
> Carl
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
> Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
