MERGE INTO 在11g中的疑问。谢谢

[michaelsheyong]

MERGE INTO T1 a
USING (SELECT * FROM T2) b
ON (a.ID = b.PARENTID)
WHEN MATCHED THEN
UPDATE SET A.SEX = 'X'
WHEN NOT MATCHED THEN
INSERT
( a.NAME, a.SEX)
VALUES
( '3', (SELECT t3.ID FROM t3 WHERE t3.ID = a.id))
说明：这三个表只是我随便建立，为了演示这个错误，此语句在10g中能正常运行，但是在11g会报错
ora-00904:"A"."ID"标识符无效，怎么解决，谢谢

[semisweet]

(SELECT t3.ID FROM t3 WHERE t3.ID = a.id)条件放到using中

[michaelsheyong]

放using?我这要通过这个语句得到一个值呢？ 自己顶顶

[semisweet]

mergeinto的语法可以理解为一个单行update语句+一个单行insert语句，需要更新的内容都从using中来，以on后的条件做关联
试着改成
MERGE INTO T1 a
USING (SELECT t2.parentid,t3.id FROM T2,t3
where t2.parentid=t3.id) b
ON (a.ID = b.PARENTID)
WHEN MATCHED THEN
UPDATE SET A.SEX = 'X'
WHEN NOT MATCHED THEN
INSERT
( a.NAME, a.SEX)
VALUES
( '3', b.id)

[michaelsheyong]

thank you.