Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
af
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
--create sequence orderids as int --minvalue 1 --cycle; --select next value for orderids; --select next value for orderids; create table MyOrders( orderid INT NOT NULL PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL ) DECLARE @Orders AS TABLE( orderid INT NOT NULL PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL, shipcountry varchar(100) ); INSERT INTO @Orders(orderid, custid, empid, orderdate,shipcountry) VALUES (2, 1, 3, '20120612','Norway'), (3, 2, 2, '20120612','Norway'), (4, 3, 5, '20120612','India'); -- CTE for filtering rows -- CTE for filtering rows -- CTE for filtering rows WITH SRC AS ( SELECT * FROM @Orders WHERE shipcountry = N'Norway' ) MERGE INTO MyOrders AS TGT USING(select orderid, custid, empid, orderdate,shipcountry from SRC) SRC ON SRC.orderid = TGT.orderid WHEN MATCHED AND ( TGT.custid <> SRC.custid OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate); select * from MyOrders
View schema
Execution time: 0,02 sec, rows selected: 2, rows affected: 5, absolute service time: 0,17 sec
edit mode
|
history
|
discussion
orderid
custid
empid
orderdate
1
2
1
3
12.06.2012 00:00:00
2
3
2
2
12.06.2012 00:00:00