Hi everyone,
Can you please help me and give me a solution for this
I have 3 columns paydate, expdate, NewRenew in a table. If the paydate is greater than 90 days than exp date I need to update NewRenew column to N and If the paydate is less than 90 days than exp date I need to update NewRenew column to R.
In simple terms as in the code below, if the column D in the query is less than 90 newrenew column should be 'R' otherwise 'N'
Below is the sample data to create and insert. when I use the update as I am using it is updating all the rows in the table.
create table test_3 (Accnt_no int, expdate date, paydate date, newrenew varchar(1) ) insert into test_3 (Accnt_no,expdate,paydate,newrenew) select 1859,'20150131','20150221','N' UNION ALL select 3549,'20150219','20150302','N' UNION ALL select 9185,'20140910','20150123','N' UNION ALL select 11302,'20140910','20150317','N' select * from (select DATEDIFF( DAY, convert(date,expdate),CONVERT(date, PAYDATE)) D,* from Test_3 WHERE convert(date,paydate) > convert(date,expdate)) z where Z.D>90 ----am trying to update in this way but it is updating all the rows in the table instead of 2 rows. update Test_3 set newrenew = 'R' from (select DATEDIFF( DAY, convert(date,expdate),CONVERT(date, PAYDATE)) D,* from Test_3 WHERE convert(date,paydate) > convert(date,expdate)) z where Z.D<90