请教：关于子查询的一个sql的写法

langfanyun11

下面2个SQL错在什么地方，应该怎么修改，初学Sybase，请多指教。
把PRODUCT_NO_2,3 SERIAL_NO_2,3省略了。

1. 使用临时表#T2010，不能用它的值delete或update么。
FOR XXX AS cursXXX CURSOR FOR
  SELECT
  T1.PRODUCT_NO_1 AS T1_PRODUCT_NO_1,
  T1.SERIAL_NO_1 AS T1_SERIAL_NO_1,
  T1.SUB_PRODUCT_NO_1 AS T1_SUB_PRODUCT_NO_1,
  T1.SUB_SERIAL_NO_1 AS T1_SUB_SERIAL_NO_1,
  T2.PRODUCT_NO_1 AS T2_PRODUCT_NO_1,
  T2.SERIAL_NO_1 AS T2_SERIAL_NO_1
  FROM T1, T2
  WHERE
  T1.SUB_SERIAL_NO_1 = T2.SUB_SERIAL_NO_1
DO
  SELECT * INTO #T2010
  FROM T1
  WHERE T1.PRODUCT_NO_1 = T1_PRODUCT_NO_1
  AND T1.SERIAL_NO_1 = T1_SERIAL_NO_1
  AND T1.SUB_PRODUCT_NO_1 = T2_PRODUCT_NO_1
  AND T1.SUB_SERIAL_NO_1 = T2_SERIAL_NO_1

  DELETE
  FROM T1
  WHERE SERIAL_NO_1 = #T2010.SERIAL_NO_1
  AND SUB_SERIAL_NO_1 = #T2010.SUB_SERIAL_NO_1

END FOR ;

错误：Correlation name '#T2010' not found
SQLCODE=-142, ODBC 3 State="42S02"

---------------------------------------------------
2.  
FOR XXXX AS cursXXXX CURSOR FOR
  SELECT
  T1.PRODUCT_NO_1 AS T1_PRODUCT_NO_1,
  T1.SERIAL_NO_1 AS T1_SERIAL_NO_1,
  T1.SUB_PRODUCT_NO_1 AS T1_SUB_PRODUCT_NO_1,
  T1.SUB_SERIAL_NO_1 AS T1_SUB_SERIAL_NO_1,
  T2.PRODUCT_NO_1 AS T2_PRODUCT_NO_1,
  T2.SERIAL_NO_1 AS T2_SERIAL_NO_1
  FROM T1, T2
  WHERE
  T1.SUB_SERIAL_NO_1 = T2.SUB_SERIAL_NO_1
FOR UPDATE
DO
  DELETE
  FROM T1
  WHERE T1.PRODUCT_NO_1 = T1_PRODUCT_NO_1
  AND T1.SERIAL_NO_1 = T1_SERIAL_NO_1
  AND T1.SUB_PRODUCT_NO_1 = T2_PRODUCT_NO_1
  AND T1.SUB_SERIAL_NO_1 = T2_SERIAL_NO_1

  UPDATE T1
  SET T1_SUB_PRODUCT_NO_1 = T2_PRODUCT_NO_1,
  T1_SUB_SERIAL_NO_1 = T2_SERIAL_NO_1
  WHERE CURRENT OF cursXXXX ;
END FOR ;

错误:Update operation attempted on non-updatable query
  SQLCODE=-192, ODBC 3 State="42000"

试图去更新一个不可更新的查询。是什么原因导致的啊？不明白错在哪儿。
-----
Error 192

"Update operation attempted on non-updatable query"

Probable cause

You attempted an insert, update, or delete operation on a query that is implicitly read-only. Queries that contain DISTINCT, GROUP BY, HAVING, EXCEPT, INTERSECT or UNION, or that contain aggregate functions, or that involve a join, are implicitly read-only. If the query references a view then the view may be non-updatable.

mdkii

1，试下 delete T1 from  #T2010 T2 where T1....=T2.........and.....
2，有可能是版本问题，请升级至15版本。

langfanyun11

1.
SELECT * INTO #T2010
  FROM T1
  WHERE T1.PRODUCT_NO_1 = T1_PRODUCT_NO_1
  AND T1.SERIAL_NO_1 = T1_SERIAL_NO_1
  AND T1.SUB_PRODUCT_NO_1 = T2_PRODUCT_NO_1
  AND T1.SUB_SERIAL_NO_1 = T2_SERIAL_NO_1；

  DELETE T1
  FROM #T2010
  WHERE SERIAL_NO_1 = #T2010.SERIAL_NO_1
  AND SUB_SERIAL_NO_1 = #T2010.SUB_SERIAL_NO_1
....
还是报错:
Could not execute statement.
Item '#T2010' already exists
SQLCODE=-110 ...

2.版本:
Sybase IQ/12.7.0/

版本暂时没法升级啊，希望是程序问题吧。。

langfanyun11

问题已解决，谢谢各位的帮助。