Insert and Update and Delete in a single statement with join
Posted by
Saranpselvam under
Sql Server on 4/21/2014 8:39:28 AM |
Points: 75 | Views : 2721 | Status :
[Member]
Hi
In sql we can perform insert and delete and update actions in a single sql query statements.
1.
create table documents (doctype varchar(100),docvalue int)
2.
insert into documents values
('A',100),('B',200)
3.
create table SUBdocuments (doctype varchar(100),docvalue int)
4.
insert into subdocuments values
('A',100),('B',200),('C',300)
merge documents as a
using (select doctype,docvalue from subdocuments)b
on a.doctype=b.doctype
when MATCHED then update set a.docvalue=a.docvalue+5
when not MATCHED and b.docvalue> 200 then
insert (doctype,docvalue) values(
'E',500) ;
here we created two tables named documents and subdocuments and inserted values for those tables.
and i used merge keyword . This merge keyword merged these two tables based on the condition . and then by using MATCHED keyword we performed update statement and insert statement . Please Note the word MATCHED should be in Uppercase and ";" is need at the end of the statement
doctype docvalue
A 105
B 205
E 500
Thanks