用一张表的内容修改另一张表的内容的SQL语句该如何写?

[zhuchengchuan]

现有两张InterBase表Table1,Table2字段如下:表Table1的字段为ID1,FX1;表Table2的字段为ID2,FX2,两张表都没有主键,FX1和FX2都为数字型,ID1和ID2为字符型,且表Table2是表Table1的子表,现当ID2等于ID1时用表Table2的FX2加上表Table1的FX1后再替换表Table1的FX1,请问,该SQL语句如何写?

[CuteBit]

ib 实现 update 语句的功能并不“强大”，其实在 SQL 标准中定义的 update 语句也是如此。
对于复杂的 update 可以使用存储过程实现。

[aidelphi]

用触发器啊！
很容易的！

[zhuchengchuan]

我没用过触发器,能给一个示例吗?

[aidelphi]

CREATE TRIGGER name FOR table

[ACTIVE | INACTIVE]

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE}

[POSITION number]

AS <trigger_body> terminator

<trigger_body> = [<variable_declaration_list>] <block>

<variable_declaration_list> =

DECLARE VARIABLE variable <datatype>;

[DECLARE VARIABLE variable <datatype>; 匽

<block> =

BEGIN

<compound_statement>

[<compound_statement> 匽

END

<datatype> = SMALLINT

| INTEGER

| FLOAT

| DOUBLE PRECISION

| {DECIMAL | NUMERIC} [(precision [, scale])]

| {DATE | TIME | TIMESTAMP)

| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}

[(int)] [CHARACTER SET charname]

| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]

<compound_statement> = {<block> | statement;}

[aidelphi]

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE

AFTER UPDATE AS

BEGIN

IF (OLD.SALARY <> NEW.SALARY) THEN

INSERT INTO SALARY_HISTORY

(EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE)

VALUES (OLD.EMP_NO, 'now', USER,OLD.SALARY,

(NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);

END !!