Oracle 数据库实现数据更新:update、merge

工作中遇到的数据更新,学习记录。

1、使用update进行数据更新

  1)最简单的更新

  update tablea a set a.price=1.00

  2)带条件的数据更新

  update tablea a set a.price = 2.00 where a.id='02'

  3)两张表关联更新为固定值

  update tablea a set a.price =3.00 where exits(select 1 from tableb b where a.id=b.id)

  将a,b相同id的 a表的price 字段更新为 3.00

  4)关联更新数据来源第二张表

  update tablea a set a.price=(select price from tablec c ) where exits (select 1 from tablec c where a.id=c.id)

  将a表price字段 更新为 id和c表id相同的数据

  5)关联更新多个字段

  update tablea a set ( a.price,a.type)=(select c.price,c.type from tablec c ) where exits (select 1 from tablec c where a.id=c.id)

  更新a表的price 和 type 字段  

  6)使用视图方式更新

  update (select a.price old,c.price as new from tablea a ,tablec c where a.id=c.id) set old=new

以上为自己了解到的Update使用方式,需要注意 a.id 和c.id需要一一对应。即c表只有一条id 与a表id对应,否则会报错

ORA-01427:"single-row subquery returns more than one row"

单行查询返回多行结果。是不能进行更新的。

2、merge 更新使用

  工作中要对一个字段:次数 进行更新 表数据量在 13w+ 需要两表关联 也就是 两个 13w+ 的表进行关联。

  在使用update进行更新的时候,效率问题大大降低。加上限制条件更新 100条数据还用了6-8S,所以 update并不适用。

  查阅资料看到merge 更新,便学习记录。

  MERGE 在SQL Server、Oracle数据库中可用,MySQL、PostgreSQL中不可用。可以同时进行更新和插入操作。执行效率要高于INSERT+UPDATE。

语法:

  MERGE INTO [your table-name] [rename your table here]
  USING ( [ your query ] )[rename your query-sql and using just like a table]
  ON ([conditional expression ] AND [...]...)
  WHEN MATHED THEN [here you can execute some update sql or something else ]
  WHEN NOT MATHED THEN [execute something else here ! ]
示例

merge into tablea a        ----要更新或者操作的表
using tablec c     ----源表 using (select * from tablec ) c
on (a.id=c.id)      --匹配条件 
when matched then update set a.price=c.price  --当匹配时进行更新操作
when not matched then     --不匹配进行插入操作
insert values (c.id,c.price) 

  using 后不仅可以使用 表 也可以是 视图或者子查询 using (select * from tablec ) c
  not matched 可以没有 就是当不匹配什么也不做。  

  总结:

  之前说的使用update更新100行数据都需要6-8S 使用merge 更新全部数据(13W+ 与13W+ 关联)只用了10S左右。更新效率可见要比update高很多。

  仅供学习使用。

注意: Oracle下使用 merge语句,on 后需要加括号, Navicat + Oracle 11g 无括号会报语句错误。 

以上摘自:https://www.cnblogs.com/com-xiaolanchong/p/7684130.html

补充:merge into ... delete 写法

--删除 t1 表在 t2 表内的记录
merge into test1 t1
using (select id from test2 t2 where t2.name = 'aa') t2
on (t1.id = t2.id)
when matched then
  update  set t1.name = t1.name 
  delete where t1.id =t2.id;

以上摘自:https://blog.csdn.net/stevendbaguo/article/details/64130917

实现将表Test2的name和age字段数据更新到表Test1中,按照id相等的条件

1、SQLServer多表更新方法:
语法:

UPDATE { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } 
SET { column_name = { expression | DEFAULT | NULL } | @variable = expression | @variable = column = expression } [ ,...n ] 
{ { [ FROM { < table_source > } [ ,...n ] ] [ WHERE < search_condition > ] } | [ WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } [ OPTION ( < query_hint > [ ,...n ] ) ]

例子:

update test1
set test1.name=test2.name,test1.age=test2.age
from test1 
inner join test2
on test1.id=test2.id

2、Oracle 多表更新方法:
语法:

UPDATE updatedtable 
SET (col_name1[,col_name2...])= (SELECT col_name1,[,col_name2...] 
FROM srctable [WHERE where_definition])

例子:

update test1 
set (test1.name,test1.age)=
(select test2.name,test2.age from test2 where test2.id=test1.id)

3、MySql多表更新方法:
语法:

UPDATE table_references 
SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

例子:

update test1,test2 
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id

4、通用方法:

update test1 
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)

以上摘自:https://www.cnblogs.com/willingtolove/p/10792713.html

SQLOracle

我来吐槽

*

*