MERGE INTO mv_a m_a USING(select t_a.inventory_item_id, t_a.organization_id, t_a.last_update_date, t_a.creation_date, t_a.description, t_a.segment1, t_a.inventory_item_status_code, t_a.unit_weight, t_a.PRIMARY_UOM_CODE from table_a t_a where t_a.organization_id=23 AND t_a.last_update_date>=SYSDATE -2 ) aa ON (m_a.inventory_item_id = aa.inventory_item_id AND m_a.Organization_Id = aa.organization_id)
WHEN MATCHED THEN UPDATE SET m_a.last_update_date = aa.last_update_date, m_a.DESCRIPTION = aa.DESCRIPTION, m_a.segment1 = aa.segment1, m_a.inventory_item_status_code = aa.inventory_item_status_code, m_a.unit_weight = aa.unit_weight, m_a.primary_uom_code = aa.primary_uom_code WHEN NOT MATCHED THEN INSERT (m_a.inventory_item_id, m_a.organization_id, m_a.last_update_date, m_a.creation_date, m_a.DESCRIPTION, m_a.segment1, m_a.inventory_item_status_code, m_a.unit_weight, m_a.primary_uom_code) Values(aa.inventory_item_id, aa.organization_id, aa.last_update_date, aa.creation_date, aa.DESCRIPTION, aa.segment1, aa.inventory_item_status_code, aa.unit_weight, aa.primary_uom_code);这是merge语句,针对的是物化视图直接运行的话提示违反唯一约束。我把查出来的数据挨个运行,只能插入第一条,插入第二条时就提示违反唯一约束我又用insert 语句,逐行插入,也是第一行正确,第二行就提示违反唯一约束我不太懂,不知道说的对不对,给我的感觉就是插入第一行的时候生成了rowid,插入第二行的时候生成的rowid和第一行一样数据上应该没有问题,即使有问题的话,像我这样逐行来试应该也可以避免了,我试的这两行肯定没有重复的 谢谢 谢谢
我可以自己添加 rowid吗?PS:我想知道到底怎么来看物化视图刷新失败的原因
把你的merge into 语句贴出来
USING(select t_a.inventory_item_id,
t_a.organization_id,
t_a.last_update_date,
t_a.creation_date,
t_a.description,
t_a.segment1,
t_a.inventory_item_status_code,
t_a.unit_weight,
t_a.PRIMARY_UOM_CODE
from table_a t_a
where t_a.organization_id=23
AND t_a.last_update_date>=SYSDATE -2
) aa
ON (m_a.inventory_item_id = aa.inventory_item_id
AND m_a.Organization_Id = aa.organization_id)
WHEN MATCHED THEN
UPDATE SET
m_a.last_update_date = aa.last_update_date,
m_a.DESCRIPTION = aa.DESCRIPTION,
m_a.segment1 = aa.segment1,
m_a.inventory_item_status_code = aa.inventory_item_status_code,
m_a.unit_weight = aa.unit_weight,
m_a.primary_uom_code = aa.primary_uom_code
WHEN NOT MATCHED THEN
INSERT (m_a.inventory_item_id,
m_a.organization_id,
m_a.last_update_date,
m_a.creation_date,
m_a.DESCRIPTION,
m_a.segment1,
m_a.inventory_item_status_code,
m_a.unit_weight,
m_a.primary_uom_code)
Values(aa.inventory_item_id,
aa.organization_id,
aa.last_update_date,
aa.creation_date,
aa.DESCRIPTION,
aa.segment1,
aa.inventory_item_status_code,
aa.unit_weight,
aa.primary_uom_code);这是merge语句,针对的是物化视图直接运行的话提示违反唯一约束。我把查出来的数据挨个运行,只能插入第一条,插入第二条时就提示违反唯一约束我又用insert 语句,逐行插入,也是第一行正确,第二行就提示违反唯一约束我不太懂,不知道说的对不对,给我的感觉就是插入第一行的时候生成了rowid,插入第二行的时候生成的rowid和第一行一样数据上应该没有问题,即使有问题的话,像我这样逐行来试应该也可以避免了,我试的这两行肯定没有重复的
谢谢 谢谢