Removing the updated row
-
Hi All I have merged data into a new table say table1 from table2(need to do this every time data comes into table2..Both the tables have following columns Id,Period(which includes date and time)Constraint(flag is only for table1) Once the data is merged into table1 the data in table2 gets deleted automatically.when a new data comes into table2 it checks whether the period(only date not the time) in table1 and table2 matches..If it gets matched then we update the constraint flag of table1 to "1". then we add this new data from table2 to table1..Then I should remove the old data...what I wanted to do now is remove the data whose contraint flag is 1 from table1 I have given the code what I have done..Can some one help how to proceed MERGE INTO table1 AS t1 USING table2 AS t2 ON t1.Id=t2.UsagePointId WHEN MATCHED THEN UPDATE SET AND t1.period=t2.period t1.constraint=0 WHEN NOT MATCHED THEN INSERT (Id, period,constraint) VALUES (t2.Id,t2.period, 0); WITH x AS ( SELECT t1.constraint FROM table1 AS t1 JOIN table2 AS t2 ON t1.Id=t2.Id AND t1.period =t2.period ) UPDATE x SET constraint=1; WHERE t1.period AS DATE =upi.t2.period AS DATE