关于Update问题

[cysh]

在oracle中如何根据多表的数据来更新一个表？
如在SQL Server中有如下的更新，在oracle中如何实现？
UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

[bluelamb]

update (select titles.ytd_sales,sales.qty FROM titles, sales
WHERE titles.title_id = sales.title_id
and sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
) set ytd_sales=ytd_sales+qty

[ORARichard]

update titles a set a.ytd_dales=(select a.ytd_sales+b.qty from sales b where a.title_id=b.title_id and b.ord_date=(select max(ord_date) from sales)) where exists (select 1 from sales b where a.title_id=b.title_id and b.ord_date=(select max(ord_date) from sales)) ;

[zhpsam109]

学习！

[qiaozhiwei]

update titles
set ytd_sales = (select titles.ytd_sales + sales.qty from titles,sales
where titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

[cysh]

楼上两位的我有点看不明白，这样子，麻烦把下面的SQL Server更新,转换成oracle的语法：

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id

[gaoyongjun]

UPDATE titles a
SET ytd_sales = a.ytd_sales + (select b.qty from sales where b.title_id = a.title_id)
WHERE exists (select 1 from sales where b.title_id = a.title_id );

[gaoyongjun]

sorry ,忘记写别名了

UPDATE titles a
SET ytd_sales = a.ytd_sales + (select b.qty from sales b where b.title_id = a.title_id)
WHERE exists (select 1 from sales b where b.title_id = a.title_id );

[ORARichard]

update titles a
set a.ytd_sales=(select a.ytd_sales+b.qty from sales b
where a.title_id=b.title_id)
where exists (select 1 from sales b
where a.title_id=b.title_id)

[xing_visitor]

update ...
set column=(select ... from ... where...)

[CodeMagic]

Update titles a
set ytd_sales=ytd_sales+
nvl((select qty from sales b where a.title_id=b.title_id),a.title_id)

[cysh]

sql server语法：
UPDATE a
SET a.ytd_sales = a.ytd_sales + b.qty
FROM titles a, sales b
WHERE a.title_id = b.title_id
如果B表是一个导出表怎么办：
如：
UPDATE titles
SET ytd_sales = titles.ytd_sales + b.qty
FROM titles a, (select title_id,sales_id,sum(qty) from table_tmp group by title_id,sales_id) b
WHERE titles.title_id = b.title_id
怎么转换成oracle语法?

[cysh]

上面少写了个别名，如下：
UPDATE titles
SET ytd_sales = titles.ytd_sales + b.qty
FROM titles a, (select title_id,sales_id,sum(qty) as qty from table_tmp group by title_id,sales_id) b
WHERE titles.title_id = b.title_id

[youxia001]

CodeMagic 的比较简洁，不过要改一下
Update titles a
set ytd_sales=ytd_sales+
nvl((select qty from sales b where a.title_id=b.title_id),0)

[ORARichard]

所有的sales都换成(select title_id,sales_id,sum(qty) from table_tmp group by title_id,sales_id)就行了

[cysh]

顶起来！

[cysh]

用merge是否可以实现？

[tigerjacky]

xue xi