DB2 Update / Delete With Jion

2010-04-06  来源:本站原创  分类:Database  人气:321 

When you need to update one table based on the other table -
it is called "correlated update". You have to repeat the same "where"
logic 2 times.

update maintab m
set (m.fname, m.lname) =
 (select u.fname,u.lname from updatetab u where m.id=u.id)
where exists
 (select null from updatetab u where m.id=u.id);

or variation: use "in" expression:

update maintab m
set (m.fname, m.lname) =
 (select u.fname,u.lname from updatetab u where m.id=u.id)
where m.id in
 (select u.id from updatetab);

or delete expression:

delete from maintab m
where exists
 (select null from updatetab u where m.id=u.id);

Note: If you don't include the 2nd "where" clause - then ALL rows in the
main table will be updated (putting NULLs in all rows which are not part of the join)
======================
Please note: the following Sybase syntax DOES NOT WORK in DB2:

update maintab
                set m.fname=u.fname, m.lname=u.lname
                from  maintab m, updates u
                where m.id=u.id

======================

How to insert a row - but only if the row with the value of a file doesn't exist:

insert into mytable (mycolumn)
select '12345' from table (values 1) as dummy
where not exists (select 1 from mytable where mycolumn='12345')
相关文章
  • DB2 Update / Delete With Jion 2010-04-06

    When you need to update one table based on the other table - it is called "correlated update". You have to repeat the same "where" logic 2 times. update maintab m set (m.fname, m.lname) = (select u.fname,u.lname from updatetab u where

  • Priority is the default mysql query update (insert.update.delete) precedence relations 2011-05-06

    First or update query (insert, update, delete) Priority: MySQL also allows changing the statement to the priority scheduling, which allows queries from multiple clients to better collaborate, such as a single client would not lock and wait for a long

  • mssql trigger insert update delete 2011-02-10

    Create an insert update delete trigger on mssql Question: did not know there is no mssql cursor in a cursor like the oracle of the function of type% tablename please enlighten me? 1 insertion (Insert) Inserted table has data, Deleted table no data (2

  • Determine the type of trigger: insert update delete 2011-04-17

    Contains a trigger mechanism to insert update delete trigger internal, how to determine the trigger of the trigger in the end is insert, delete, or update it? We can adopt the following ways: Insert: IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS

  • The default priority mysql query or update (insert.update.delete) precedence relations 2011-05-06

    Priority or update query (insert, update, delete) Priority: MySQL also allows you to change the scheduling priority statement, which can make queries from multiple clients to better collaborate, such as a single client does not lock while waiting for

  • hibernate3 bulk update / delete data 2011-08-18

    New Hibernate3.0 the ANTLR-based HQL / SQL query translator, in the Hibernate configuration file, hibernate.query.factory_class attributes used to select the query translator. (1) Select the query translator Hibernate3.0: hibernate.query.factory_clas

  • About oracle in the select, update, delete, insert usage 2011-08-25

    Database insert, select, update, delete are based on the search line by line, in line with where the conditions of data, Conform to the related operations, which do not conform ignored, Note: When using the equal sign in the where condition to do whe

  • MSSQL2005 INSERT,UPDATE,DELETE 之OUTPUT子句使用实例 2014-10-13

    MSSQL2005 INSERT,UPDATE,DELETE使用实例,大家可以看下. -->Title:Generating test data -->Author:wufeng4552 -->Date :2009-10-07 15:16:26 if object_id('ta')is not null drop table ta go create table ta(ID int identity,[name] varchar(10)) insert ta([name]) select

  • SQLServer 2008中SQL增强之三 Merge(在一条语句中使用Insert,Update,Delete) 2014-12-02

    SQLServer 2008中SQL增强之三 Merge(在一条语句中使用Insert,Update,Delete) SQL Server 2008提供了一个增强的SQL命令Merge,用法参看MSDN:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 功能:根据与源表联接的结果,对目标表执行插入.更新或删除操作.例如,根据在另一个表中找到的差异在一个表中插入.更新或删除行,可以对两个表进行同步. 我们看一个例子,假如,有一总产品列表,一

  • hibernate Executing an update/delete query 2013-08-20

    org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query at org.springframework.orm.jpa.EntityManagerFactoryUtil

  • Insert or update existing update, delete does not exist 2010-05-11

    In the development process, often encounter such problems: Oracle table operation of the record, if there's Hua Bu would add, if there is updates. After several years of work experience, so I know: I encounter any problems not a problem, I believe th

  • Hibernate3.0 bulk update, delete 2010-11-07

    Session session = sessionFactory.openSession (); Transaction tx = session.beginTransaction (); String hqlUpdate = "update Customer set name =: newName where name = ldName"; int updatedEntities = s.createQuery (hqlUpdate) . SetString ("newNa

  • Spring + Hibernate + Flex, update.delete action can not be a lasting solution to the database 2011-04-17

    The solution is to have the transaction mechanism. . . I spring2.5.6 + hibernte3.3, did not use spring's hibernateTemplate and HibernateDaoSupport, just use its AnnotationSessionFactoryBean, add delete check, no problems, just an update on the invali

  • Implementation of DB2 to delete duplicate data 2011-05-25

    DB2 delete duplicate data so that we often use the operation, the following will teach you to delete duplicate data in DB2, and I hope to learn DB2 you help delete duplicate data. Remove duplicate data using ROW_NUMBER assumptions table TAB has a, b,

  • Trigger basic syntax (for insert, update, delete) 2011-04-01

    I. Overview of Trigger trigger (trigger) is a special stored procedure that is called by the program's implementation is not, nor is started manually, but by the events to trigger, such as when to operate on a table (insert, delete, update ) will be

  • MySQL数据库INSERT.UPDATE.DELETE以及REPLACE语句的用法详解 2014-02-17

    本篇文章是对MySQL数据库INSERT.UPDATE.DELETE以及REPLACE语句的用法进行了详细的分析介绍,需要的朋友参考下 MySQL数据库insert和update语句引:用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句.言外之意,就是对数据进行修改.在标准的SQL中有3个语句,它们是INSERT.UPDATE以及DELETE. 用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELE

  • SQL Server中的XML数据进行insert.update.delete操作实现代码 2014-06-29

    SQL Server 2005/2008增加了对XML数据的支持,同时也新增了几种操作XML的方法,本文主要以SQL Server 2008为例介绍如何对XML数据进行insert.update.delete SQL Server中新增加了XML.Modify()方法,分别为xml.modify(insert),xml.modify(delete),xml.modify(replace)对应XML的插入,删除和修改操作. 本文以下面XML为例,对三种DML进行说明: declare @XMLVa

  • SQL Server中的XML数据进行insert.update.delete 2014-08-19

    SQL Server 2005/2008增加了对XML数据的支持,同时也新增了几种操作XML的方法,本文主要以SQL Server 2008为例介绍如何对XML数据进行insert.update.delete. SQL Server中新增加了XML.Modify()方法,分别为xml.modify(insert),xml.modify(delete),xml.modify(replace)对应XML的插入,删除和修改操作. 本文以下面XML为例,对三种DML进行说明: declare @XMLV

  • yii框架builder.update.delete使用方法 2014-11-18

    这篇文章主要介绍了yii框架builder.update.delete使用方法,需要的朋友可以参考下 Yii自带的query builder还是很好用的,省去了拼sql的过程,今天在写一个语句的时候遇到这样一个问题 $connection = Yii::app()->db; $command = $connection->createCommand(); $operate_rst = 0; if(!empty($_POST['lid'])){ $operate_rst = $command-&

  • 关于mybatis 动态 sql 的一些陷阱:防止批量update,delete,select... 2012-04-05

    问题产生场景: 昨天支付中心发起退款回调时,引起了我们这边一个bug: 有两笔退款异常,支付中心发起第一笔异常的回调的时候就把我们这边两笔退款异常对应的订单的状态全部给修改 了.当支付中心对第二笔异常回调的时候回调程序发现订单状态已经改变发出了一个异常邮件,然后回调就终止 了,所以数据库呈一个不一致状态:订单状态已改变,但没有记录订单日志,也没有记录退款状态.然后大家就来寻找这个bug,过程挺有意思的. 首先我们请DBA从订单号,订单Id和时间多个条件查数据库的日志,想找出是哪个系统发出的这个更