这是sybase里的写法UPDATE M_IMPORT_ITEM_H004
SET CENTER_ID=A.ITEM_CODE ,ITEM_CLASS=A.ITEM_CLASS
FROM T_CONTRAST_H004 A, M_IMPORT_ITEM_H004 B
WHERE
A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID在Oracle里没有Update from的写法
该怎么写?
UPDATE M_IMPORT_ITEM_H004 A
SET (CENTER_ID,ITEM_CLASS) =(
select (A.ITEM_CODE,A.ITEM_CLASS)
FROM T_CONTRAST_H004 A ,M_IMPORT_ITEM_H004 B
WHERE A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID)这样写 它报缺少右括号
SET CENTER_ID=A.ITEM_CODE ,ITEM_CLASS=A.ITEM_CLASS
FROM T_CONTRAST_H004 A, M_IMPORT_ITEM_H004 B
WHERE
A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID在Oracle里没有Update from的写法
该怎么写?
UPDATE M_IMPORT_ITEM_H004 A
SET (CENTER_ID,ITEM_CLASS) =(
select (A.ITEM_CODE,A.ITEM_CLASS)
FROM T_CONTRAST_H004 A ,M_IMPORT_ITEM_H004 B
WHERE A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID)这样写 它报缺少右括号
set (t1.center_id,t1.item_class)=(select t2.item_code, t2.item_class
from T_CONTRAST_H004 t2
where t2.item_style=t1.catalog_class and t2.item_id=t1.item_code)
where exists ( select 1 from T_CONTRAST_H004 t3
where t3.item_style=t1.catalog_class and t3.item_id=t1.item_code);
FROM T_CONTRAST_H004 A, M_IMPORT_ITEM_H004 B
WHERE
A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID
产生多条记录,就不能更新的。一般都是要和更新表M_IMPORT_ITEM_H004有连接条件的,不然何如确定更新的是
这个表的哪个列
SET (CENTER_ID,ITEM_CLASS) =(
select (B.ITEM_CODE,B.ITEM_CLASS)
FROM T_CONTRAST_H004 B
WHERE A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID)
where exists(select 1 from T_CONTRAST_H004 C where A.ITEM_STYLE=C.CATALOG_CLASS
AND C.ITEM_CODE=A.ITEM_ID)--后面加个where exists(select 1 from T_CONTRAST_H004 C where A.ITEM_STYLE=C.CATALOG_CLASS
AND C.ITEM_CODE=A.ITEM_ID) 是防止把不符合的也更新了,只更新M_IMPORT_ITEM_H004 在T_CONTRAST_H004符合的
UPDATE M_IMPORT_ITEM_H004 A
SET (CENTER_ID,ITEM_CLASS) =(
select (B.ITEM_CODE,B.ITEM_CLASS)
FROM T_CONTRAST_H004 B
WHERE A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID)
where exists(select 1 from T_CONTRAST_H004 C where A.ITEM_STYLE=C.CATALOG_CLASS
AND C.ITEM_CODE=A.ITEM_ID)
merge into M_IMPORT_ITEM A using T_CONTRAST_H004 B on(A.ITEM_STYLE=B.CATALOG_CLASS
AND B.ITEM_CODE=A.ITEM_ID)
when matched then update set A.CENTER_ID=B.ITEM_CODE ,A.ITEM_CLASS=B.ITEM_CLASS