UPDATE命令出现语法错误

Question

这是Informix更新的正确语法吗？

UPDATE da1010
SET d1.da1_prcven = s9.b9_cm1, d1.datvig = s9.b9_data
FROM da1010 d1
INNER JOIN sb1010 s1
    ON (d1.da1_codpro = s1.b1_cod AND s1.d_e_l_e_t_ <> '*')
INNER JOIN sb9010 S9
    ON (s9.b9_filial = '0101' AND s9.b9_cm1 <> 0 AND d1.da1_codpro = s9.b9_cod AND d1.da1_datvig < s9.b9_data AND s1.b1_locpad = s9.b9_local AND s9.d_e_l_e_t_ <> '*')
WHERE d1.da1_codtab = '009'
    AND d1.d_e_l_e_t_ <> '*' 
    AND s9.b9_data = (SELECT MAX(b9_data) FROM SB9010)

当我给出执行查询的命令时，编辑器会返回一个语法错误(-201:语法错误已经发生)。

Answer 1

在对informix的文档进行了大量的查询和研究之后，我找到了问题的解决方案，我在这里与您分享这个主题。

CREATE PROCEDURE proc_update_table()
DEFINE x_cod    varchar(15) ;    --declaration local variable
DEFINE x_date   varchar(8)  ;
DEFINE x_vlr    float       ;

FOREACH    --selects rows according to query
SELECT b9_cod, b9_data, b9_cm1 
INTO x_cod, x_date, x_vlr
FROM SB9010
INNER JOIN DA1010 ON (b9_cod = da1_codpro AND da1_datvig < b9_data)
INNER JOIN SB1010 ON (b9_cod = b1_cod AND b9_local = b1_locpad)
WHERE b9_cm1 <> '0'
AND SB9010.d_e_l_e_t_ <> '*'
AND b9_data = (SELECT MAX(b9_data) FROM SB9010)

UPDATE DA1010    --update fields with variables values
SET da1_prcven = x_vlr, da1_datvig = x_date   
AND da1_codpro = x_cod
AND da1_datvig < x_date 
AND d_e_l_e_t_ <> '*';
END FOREACH;
END PROCEDURE;

Answer 2

我看到您使用请求的信息更新表da1010，但在这一行中：

设置d1.da1_prcven = s9.b9_cm1，d1.datvig = s9.b9_data

您是否更新了表D1的信息，而不是da1010表的信息。正确的更新如下：

UPDATE da1010
SET da1010.da1_prcven = s9.b9_cm1, da1010.datvig = s9.b9_data
FROM da1010 d1
INNER JOIN sb1010 s1
    ON (d1.da1_codpro = s1.b1_cod AND s1.d_e_l_e_t_ <> '*')
INNER JOIN sb9010 S9
    ON (s9.b9_filial = '0101' AND s9.b9_cm1 <> 0 AND d1.da1_codpro = s9.b9_cod AND d1.da1_datvig < s9.b9_data AND s1.b1_locpad = s9.b9_local AND s9.d_e_l_e_t_ <> '*')
WHERE d1.da1_codtab = '009'
    AND d1.d_e_l_e_t_ <> '*' 
    AND s9.b9_data = (SELECT MAX(b9_data) FROM SB9010)

PS:您不能在update中使用别名，只能在.之后使用

Answer 3

另一个选项是使用MERGE：

 MERGE INTO dept d
     USING dept_online o
     ON (d.deptno = o.deptno)
     WHEN MATCHED THEN
          UPDATE SET d.dname = o.dname, d.loc = o.loc;