|
View previous topic :: View next topic
|
| Author |
Message |
karnataka
New User
Joined: 15 Sep 2006
Posts: 20
Location: bangalore
|
|
|
|
Hi All,
My requirement is as below
TABLE1
*COLM1  PK
*COLM2  PK
IND
TABLE2
*COLM1  PK
*COLM2 PK
*COLM3  PK
RECORD_TIMESTAMP
I need to update TABLE1.IND as ‘1’ if
1) The Table1.IND is not an ‘1’ already
2) The Table1 has no TABLE2 records that has a record_timestamp within the last 13 months.
Can any one help me out in this.
Thanks,
Jyothi |
|
| Back to top |
|
 |
karnataka
New User
Joined: 15 Sep 2006
Posts: 20
Location: bangalore
|
|
|
|
Table description was not proper in above mail. '-->' symbol has replaced with some numeric value .. Please have a look below for correct description
TABLE1
*COLM1 --> PK
*COLM2 --> PK
IND
TABLE2
*COLM1 --> PK
*COLM2 -->PK
*COLM3 --> PK
RECORD_TIMESTAMP |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi Jyoti,
Try this
| Code: |
UPDATE TABLE1
SET IND = '1'
WHERE IND <>'1'
AND (COLM1,COLM2) NOT IN (SELECT COLM1,COLM2 FROM TABLE2
WHERE RECORD_TIMESTAMP BETWEEN CURRENT TIMESTAMP AND (RECORD_TIMESTAMP - 13 MONTHS); |
I havent tested it though.... |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
"IN-list" like above (a small error : it should say "current timestamp - 13 months" or
if you like performant queries use "not exists"
| Code: |
UPDATE TABLE1 A
SET IND = '1'
WHERE IND <>'1'
AND not exists (SELECT 1 FROM TABLE2 B
WHERE a.colm1 = b.colm1 and a.colm2 = b.colm2
and RECORD_TIMESTAMP > (CURRENT TIMESTAMP - 13 MONTHS)); |
|
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Thanks GuyC... I just copy pasted and it went un-noticed... |
|
| Back to top |
|
|
|
|
