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

 

 

 

 

UPDATE Test_3
SET newrenew = CASE 
		WHEN DATEDIFF(DAY, convert(DATE, expdate), CONVERT(DATE, PAYDATE)) < 90
			THEN 'R'
		ELSE 'N'
		END
FROM test_3

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you need to read and download the PDF for: 
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

>> I have 3 columns pay_date, expiry_date, renewal_flg in a table.<<

No, you do not have a table. A table has a key by definition. What you did post is awful. An accounting number is an identifier and you do no math on it, so it should never be a numeric. We do not use flags in RDBMS! That is assembly language programming. SQL is a logical predicate based language that computes a state of being as part of a query. Your mind set is still in punch cards and mag tapes where we had to physically mark things in hardware. That is your assembly language renewal flag a hole in the last column of a 1950's punch card in 2015 SQL Then the punch cards are passed to the next step in the sequence of procedural steps. 

We now have modern INSERT INTO syntax and CAST() in place of the old Sybase CAST() string function. The old Sybase UPDATE..FROM.. does not work! Google it. The ANSI/ISO Standard SQL uses only the yyyy-mm-dd style date display format; what you had will also work in T-SQL, but might not port and makes you look like a dialect speaker. 

 CREATE TABLE Test_3
 (account_nbr CHAR(16) NOT NULL  PRIMARY KEY, -- typical credit card length
  expiry_date DATE NOT NULL, 
  pay_date DATE NOT NULL);

Here is the correct syntax for insertions since the SQL-92 standards:

 INSERT INTO Test_3 (account_nbr, expiry_date, pay_date)
VALUES
 (1859, '2015-01-31', '2015-02-21'), 
 (3549, '2015-02-19', '2015-03-02'), 
 (9185, '2014-09-10', '2015-01-23'), 
 (11302, '2014-09-10', '2015-03-17');

Why did you cast (well, you did it with CONVERT() instead) a date to date? Why did you use SELECT * in production code? Is this what you meant?

CREATE VIEW Old_Accounts_Test_3
AS
SELECT account_nbr,  expiry_date,  pay_date,
            DATEDIFF(DAY, expiry_date, pay_date) AS account_age
  FROM Test_3
 WHERE pay_date  > expiry_date  -- is it >= or >?? 
    AND DATEDIFF(DAY, expiry_date, pay_date)  > 90;

This view is always current, required no disk writes, etc. I have been teaching SQl for a few decades, and I have see this mindset before.  You are 3-5 years away from actually being an SQL programmer. You need to do a lot of un-learning. The bad news is that if you keep coming to forums, people will feed you kludges and you will never learn. Be better than that.  

 

Hi Kumarkv,

The UPDATE statement You were trying to update that way but it was updating all the rows in the table instead of 2 rows, because the UPDATE statement is trying to UPDATE the Table Test_3 other than the derived Table Z. Please see the below sample.

--your statement
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

--in this case, you statement is equivalent to the below update Test_3, when the EXISTS clause returns ture all rows in Test_3 get updated or non gets updated when it returns false
  set newrenew = 'N'
  WHERE EXISTS(
  select 1 
  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
  )

--make some modification as below, your update will work as expected.

update Z  -- update the derived table Z
  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

SELECT * FROM TEST_3

If you have any question, feel free to let me