| date update [message #371668] |
Wed, 22 November 2000 02:59  |
lhj
Messages: 5
Registered: November 2000
|
Junior Member |
|
|
How do I update a date in table_A with a date from table_b where the date in table_A is equal to 01-JAN-1900 ( equal to infinite)?
table_A
-------------------------
typenr fromdate todate
-------------------------
2323 01-jan-1900 08-sep-1988
2122 22-feb-1977 01-jan-1900
table_b
-------------------------
typenr fromdate todate
-------------------------
2323 11-aug-1967 08-sep-1988
2122 22-feb-1977 12-dec-1998
I've tried using cursors, but without luck
Best regards
LHJ
|
|
|
|
| Re: date update [message #371669 is a reply to message #371668] |
Wed, 22 November 2000 03:58  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Based on your tables, probably:
UPDATE table_a a
SET (from_date,to_date) =
(SELECT from_date,to_date
FROM table_b b
WHERE a.typenr = b.typenr
AND ( trunc(a.from_date) = to_date('1-jan-1900','dd-mon-yyyy')
OR trunc(a.to_date) = to_date('1-jan-1900','dd-mon-yyyy'))
If the problem you are having is that your query isn't updating rows that you think it should, the problem is probably that there is a time component to the dates, which is cured by TRUNCing the dates before comparing.
Hope this helps
|
|
|
|
| Re: date update [message #371670 is a reply to message #371668] |
Wed, 22 November 2000 04:00 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Oops. Ignore the previous message.
What I meant to say was:
UPDATE table_a a
SET (from_date,to_date) =
(SELECT from_date,to_date
FROM table_b b
WHERE a.typenr = b.typenr)
WHERE ( trunc(a.from_date) = to_date('1-jan-1900','dd-mon-yyyy')
OR trunc(a.to_date) = to_date('1-jan-1900','dd-mon-yyyy'))
Hope this helps
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
