op 10-11-2015 10:53 schreef YAN HONG YE op yanhong.ye at mpsa.com: > Hi, > I have two table with same field: > > Id int, > Content text, > Inserttime datatime, > Updatetime datatime > > I want to do this: > If id not in the other table, then insert it, > if id is same, and inserttime diffirent, then insert it to the other table ; > if id is same, and updatetime diffirent, then update it to the other table ;
> I don't know if exist this tool, the target is synchronize two table to the > same except ID field. As Simon Slavin said, this is not a single command. INSERT OR REPLACE does not cover deletes. If the Id column were defined as INTEGER PRIMARY KEY, then the INSERT OR REPLACE statement can help. If I understand it right, it should not matter if you update the other table or just replace a complete row. Some filter (WHERE clause) is still needed to not rewrite the complete table. This could look like: INSERT OR REPLACE INTO t2 SELECT * FROM t1 LEFT OUTER JOIN t2 USING (id) WHERE t2.id IS NULL -- id not in the other table OR t2.inserttime <> t1.inserttime --if id is same, and inserttime diffirent OR t2/updatetime <> t1.updatetime --if id is same, and updatetime diffirent ; Or even shorter: INSERT OR REPLACE INTO t2 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ;
