update b set b.x=a.x,b.y=a.y,b.z=a.z from a join b on a.id = b.id where a.ts != b.ts
update b set x = a.x, y = a.y, z = a.z from b , a where a.id = b.id and a.ts <> b.ts
UPDATE B SET B.X=M.X,B.Y=M.Y,B.Z=M.Z FROM (SELECT * FROM A ,B WHERE A.ID=B.ID AND A.TS<>B.TS)M
UPDATE B SET B.X=M.X,B.Y=M.Y,B.Z=M.Z FROM (SELECT A.* FROM A ,B WHERE A.ID=B.ID AND A.TS<>B.TS)M
IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='tb1') DROP TABLE [tb1] IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='tb2') DROP TABLE [tb2] CREATE TABLE tb1(ID INT, X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10) ,TS DATETIME) CREATE TABLE tb2(ID INT, X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10) ,TS DATETIME)INSERT Tb1 SELECT 1,'1','1','1','2010-1-1 1:00' UNION ALL SELECT 2,'1','1','1','2010-1-2 1:00' UNION ALL SELECT 3,'1','1','1','2010-1-3 1:00' UNION ALL SELECT 4,'1','1','1','2010-1-4 1:00' GO INSERT Tb2 SELECT 1,'2','2','2','2010-1-1 1:00' UNION ALL SELECT 2,'2','2','2','2010-1-2 1:00' UNION ALL SELECT 3,'2','2','2','2010-1-3 1:00' UNION ALL SELECT 4,'2','2','2','2010-1-4 2:00' GOUPDATE tb2 SET x=tb1.x,y=tb1.y,z=tb1.z FROM tb1 JOIN tb2 ON tb1.id=tb2.id WHERE (tb1.ts<>tb2.ts)SELECT * FROM tb2/* ID X Y Z TS ----------- ---------- ---------- ---------- ------------------------------------------------------ 1 2 2 2 2010-01-01 01:00:00.000 2 2 2 2 2010-01-02 01:00:00.000 3 2 2 2 2010-01-03 01:00:00.000 4 1 1 1 2010-01-04 02:00:00.000*/
update b
set x = a.x,
y = a.y,
z = a.z
from b , a
where a.id = b.id and a.ts <> b.ts
SET B.X=M.X,B.Y=M.Y,B.Z=M.Z
FROM (SELECT * FROM A ,B
WHERE A.ID=B.ID AND A.TS<>B.TS)M
SET B.X=M.X,B.Y=M.Y,B.Z=M.Z
FROM (SELECT A.* FROM A ,B
WHERE A.ID=B.ID AND A.TS<>B.TS)M
DROP TABLE [tb1]
IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='tb2')
DROP TABLE [tb2]
CREATE TABLE tb1(ID INT,
X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10) ,TS DATETIME)
CREATE TABLE tb2(ID INT,
X VARCHAR(10),Y VARCHAR(10),Z VARCHAR(10) ,TS DATETIME)INSERT Tb1
SELECT 1,'1','1','1','2010-1-1 1:00' UNION ALL
SELECT 2,'1','1','1','2010-1-2 1:00' UNION ALL
SELECT 3,'1','1','1','2010-1-3 1:00' UNION ALL
SELECT 4,'1','1','1','2010-1-4 1:00'
GO
INSERT Tb2
SELECT 1,'2','2','2','2010-1-1 1:00' UNION ALL
SELECT 2,'2','2','2','2010-1-2 1:00' UNION ALL
SELECT 3,'2','2','2','2010-1-3 1:00' UNION ALL
SELECT 4,'2','2','2','2010-1-4 2:00'
GOUPDATE tb2 SET x=tb1.x,y=tb1.y,z=tb1.z FROM tb1 JOIN tb2 ON tb1.id=tb2.id
WHERE (tb1.ts<>tb2.ts)SELECT * FROM tb2/*
ID X Y Z TS
----------- ---------- ---------- ---------- ------------------------------------------------------
1 2 2 2 2010-01-01 01:00:00.000
2 2 2 2 2010-01-02 01:00:00.000
3 2 2 2 2010-01-03 01:00:00.000
4 1 1 1 2010-01-04 02:00:00.000*/