Cache Update Query

Question

Question

Jude Mukkadayil · Oct 29, 2019

Hi,

     I am trying to update an year on a date field in a cache database table but it showing an error message. But the functions are working on select query. The query I used is

update RB_ResEffDateSessPayorRestr SET RESTR_DATETo = DATEADD(YYYY,1,RESTR_DATETo) where YEAR(RESTR_DATETo)=2020

I tried to update only the year which are 2020.

Can anyone please help me, Is there any error in the query?

Thanks

Discussion (12)2

Log in or sign up to continue

Robert Cemper · Oct 29, 2019

If RESTR_DATETo you try to change is part of the Idkey you can't do an UPDATE on it.

Instead you need an INSERT with new Contents and DELETE of the old.

1 0

Vitaliy Serdtsev · Oct 30, 2019

Try this:

update RB_ResEffDateSessPayorRestr set RESTR_DATETo=to_date(DATEADD('year',1,RESTR_DATETo),'YYYY-MM-DD') where YEAR(RESTR_DATETo)=2020

Or this:

update RB_ResEffDateSessPayorRestr set RESTR_DATETo=%odbcin(DATEADD('year',1,RESTR_DATETo)) where YEAR(RESTR_DATETo)=2020

0 0

score 0 · Answer 1 · 2019-10-29T02:01:44-04:00

Hi @Jude Mukkadayil

Please try this;

DATEADD will return a timestamp, such as '2020-07-26 00:00:00',

Just need to convert it back to Date and insert

UPDATE RB_ResEffDateSessPayorRestr  
SET RESTR_DATETo = DATE(DATEADD('year',1,RESTR_DATETo)) 
WHERE YEAR(RESTR_DATETo)=2020

Hope this helps.

Rizmaan.

score 0 · Answer 2 · 2019-10-29T02:21:11-04:00

Still its not working.

I think its because of looping. Since I am using the same field in the where condition , that may be the issue.

Any idea?

score 0 · Answer 3 · 2019-10-29T03:28:18-04:00

Rizmaan Marikar · Oct 29, 2019

what is the data type of

RESTR_DATETo

and can you post the error that you are getting

0 0

score 0 · Answer 4 · 2019-10-29T18:12:41-04:00

Jude Mukkadayil · Oct 29, 2019

date is the datatype.

0 0

score 0 · Answer 5 · 2019-10-30T02:53:24-04:00

Rizmaan Marikar · Oct 30, 2019

Hi,

Is it possible to share the entire query / code that you are using to perform the update?

0 0

score 0 · Answer 6 · 2019-10-29T19:17:40-04:00

Jude Mukkadayil · Oct 29, 2019

Around 10K data .

0 0

score 0 · Answer 7 · 2019-10-30T03:43:40-04:00

Robert Cemper · Oct 30, 2019

It's a matter of design of the table.

pls. provide the table description

0 0

score 0 · Answer 8 · 2019-10-29T21:25:43-04:00

Hello Jude

What is the error?
Do you have change control enabled?

if your environment has CCR enabled you may need to create a change session before running an update query.

score 0 · Answer 9 · 2019-10-29T21:39:50-04:00

Error: [C:\Program Files (x86)\Synametrics Technologies\WinSQL\Winsql.exe]
[SQLCODE: <-105>:<Field validation failed in UPDATE>]
[Location: <ServerLoop>]
[%msg: <Field 'SQLUser.RB_ResEffDateSessPayorRestr.RESTR_DateTo' (value '2039-01-07 00:00:00') failed validation>] (State:S1000, Native Code: 69)

score 0 · Answer 10 · 2019-10-30T17:23:27-04:00

Many Thanks . It worked very well.

I used this one only

update RB_ResEffDateSessPayorRestr set RESTR_DATETo=to_date(DATEADD('year',1,RESTR_DATETo),'YYYY-MM-DD') where YEAR(RESTR_DATETo)=2020