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

本帖最后由 langfanyun11 于 2010-11-11 15:13 编辑

各位好，

  本人初学sybase，对sybase的sql不怎么明白，查了一些资料，好像子查询和oracle不太一样。
  请教一个sql的写法。

有2个表，table_a和table_b
table_a的字段： PRODUCT1, PRODUCT2, PRODUCT3,
  SERIAL1, SERIAL2, SERIAL3,
  SUB_PRODUCT1, SUB_PRODUCT2, SUB_PRODUCT3,
  SUB_SERIAL1, SUB_SERIAL2, SUB_SERIAL3,
  XXX1, XXX2...

table_b的字段： PRODUCT1, PRODUCT2, PRODUCT3,
  SERIAL1, SERIAL2, SERIAL3,
  SUB_PRODUCT1, SUB_PRODUCT2, SUB_PRODUCT3,
  SUB_SERIAL1, SUB_SERIAL2, SUB_SERIAL3,
  YYY1, YYY2...
两个表的PK都是SERIAL1,SERIAL2,SERIAL3,SUB_SERIAL1,SUB_SERIAL2,SUB_SERIAL3

想从table_a中检索出与table_b中SUB_SERIAL1,2,3值相同的数据，
并且用table_b的PRODUCT1,2,3, SERIAL_1,2,3字段去更新对应的table_a表的SUB_PRODUCT1,2,3, SUB_SERIAL_1,2,3字段。
但是如果有SERIAL,SUB_SERIAL1,2,3冲突时，需要删除table_a中冲突的那条记录，再更新。


因为数据量很大，并且循环操作，所以性能方面也需要考虑。

先谢谢了。

试着写的SQL如下,但是不对。
FOR XXXX AS cursTABLE_A CURSOR FOR
  SELECT
  TABLE_A.DIVISION AS TABLE_A_DIVISION,
  TABLE_A.PRODUCT_NO1 AS TABLE_A_PRODUCT_NO1,
  TABLE_A.PRODUCT_NO2 AS TABLE_A_PRODUCT_NO2,
  TABLE_A.PRODUCT_NO3 AS TABLE_A_PRODUCT_NO3,
  TABLE_A.SERIAL_NO1 AS TABLE_A_SERIAL_NO1,
  TABLE_A.SERIAL_NO2 AS TABLE_A_SERIAL_NO2,
  TABLE_A.SERIAL_NO3 AS TABLE_A_SERIAL_NO3,
  TABLE_A.SUB_DIVISION AS TABLE_A_SUB_DIVISION,
  TABLE_A.SUB_PRODUCT_NO1 AS TABLE_A_SUB_PRODUCT_NO1,
  TABLE_A.SUB_PRODUCT_NO2 AS TABLE_A_SUB_PRODUCT_NO2,
  TABLE_A.SUB_PRODUCT_NO3 AS TABLE_A_SUB_PRODUCT_NO3,
  TABLE_A.SUB_SERIAL_NO1 AS TABLE_A_SUB_SERIAL_NO1,
  TABLE_A.SUB_SERIAL_NO2 AS TABLE_A_SUB_SERIAL_NO2,
  TABLE_A.SUB_SERIAL_NO3 AS TABLE_A_SUB_SERIAL_NO3,
  TABLE_B.DIVISION AS TABLE_B_DIVISION,
  TABLE_B.PRODUCT_NO1 AS TABLE_B_PRODUCT_NO1,
  TABLE_B.PRODUCT_NO2 AS TABLE_B_PRODUCT_NO2,
  TABLE_B.PRODUCT_NO3 AS TABLE_B_PRODUCT_NO3,
  TABLE_B.SERIAL_NO1 AS TABLE_B_SERIAL_NO1,
  TABLE_B.SERIAL_NO2 AS TABLE_B_SERIAL_NO2,
  TABLE_B.SERIAL_NO3 AS TABLE_B_SERIAL_NO3
  FROM TABLE_A, TABLE_B
  WHERE
  TABLE_A.SUB_SERIAL_NO1 = TABLE_B.SUB_SERIAL_NO1 AND
  TABLE_A.SUB_SERIAL_NO2 = TABLE_B.SUB_SERIAL_NO2 AND
  TABLE_A.SUB_SERIAL_NO3 = TABLE_B.SUB_SERIAL_NO3
FOR UPDATE
DO
  DELETE
  FROM TABLE_A
  WHERE
  TABLE_A.DIVISION = TABLE_A_DIVISION AND
  TABLE_A.PRODUCT_NO1 = TABLE_A_PRODUCT_NO1 AND
  TABLE_A.PRODUCT_NO2 = TABLE_A_PRODUCT_NO2 AND
  TABLE_A.PRODUCT_NO3 = TABLE_A_PRODUCT_NO3 AND
  TABLE_A.SERIAL_NO1 = TABLE_A_SERIAL_NO1 AND
  TABLE_A.SERIAL_NO2 = TABLE_A_SERIAL_NO2 AND
  TABLE_A.SERIAL_NO3 = TABLE_A_SERIAL_NO3 AND
  TABLE_A.SUB_DIVISION = TABLE_B_DIVISION AND
  TABLE_A.SUB_PRODUCT_NO1 = TABLE_B_PRODUCT_NO1 AND
  TABLE_A.SUB_PRODUCT_NO2 = TABLE_B_PRODUCT_NO2 AND
  TABLE_A.SUB_PRODUCT_NO3 = TABLE_B_PRODUCT_NO3 AND
  TABLE_A.SUB_SERIAL_NO1 = TABLE_B_SERIAL_NO1 AND
  TABLE_A.SUB_SERIAL_NO2 = TABLE_B_SERIAL_NO2 AND
  TABLE_A.SUB_SERIAL_NO3 = TABLE_B_SERIAL_NO3 ;

  UPDATE TABLE_A
  SET TABLE_A_SUB_DIVISION = TABLE_B_DIVISION,
  TABLE_A_SUB_PRODUCT_NO1 = TABLE_B_PRODUCT_NO1,
  TABLE_A_SUB_PRODUCT_NO2 = TABLE_B_PRODUCT_NO2,
  TABLE_A_SUB_PRODUCT_NO3 = TABLE_B_PRODUCT_NO3,
  TABLE_A_SUB_SERIAL_NO1 = TABLE_B_SERIAL_NO1,
  TABLE_A_SUB_SERIAL_NO2 = TABLE_B_SERIAL_NO2,
  TABLE_A_SUB_SERIAL_NO3 = TABLE_B_SERIAL_NO3
  WHERE CURRENT OF cursTABLE_A ;
END FOR ;

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