. I want to set the startdate = to the startdate of the record that has an effenddate = 20151231. Can I do something like this? See bold code

1

Update tbl_epic_coverage
Set statusid = '01',
Lastmoduserid = 'NONPYMT',
Lastmoddate = sysdate,
Reasonid = '14',
effstartdate = (Select effstartdate from tbl_epic_coverage where to_char(effenddate,'yyyymmdd') = '20151231')
Where exists (select 'x'
From tbl_epic_app a,
V_cardholder_appcnum_xref b,
Stg_nyfee_update_member_status c
Where b.appcnum = a.appcnum
And b.relationshiptypedesc = 'Self'
And c.MEMBER_ID = b.cardholderid
And c.cancel_reason like ' |NonPymt Sys Cncl Dt:%'
And a.appcnum in (5342652)
And a.statusid = '00'
And activeflag = 'Y'
And to_char(a.appentrydate,'yyyymmdd') > '20140101' )

2

Personally I would put it in the FROM clause and JOIN it - mainly because I don't trust the optimiser to always spot that something only needs to be executed once - but other than that it looks fine ot me, in principle.

(This is a Microsoft SQL forum, so there might not be any folk here who can help with the specifics of Oracle in your query)

I'm always intrigued by the Oracle method of cmoparing a date against a string constant, e.g.

AND to_char(a.appentrydate,'yyyymmdd') > '20140101'

as in MS SQL using a function on a column in that way, in the WHERE clause, that would mean that any index on a.appentrydate would not be used. In MS SQL its perfectly valid to say

AND a.appentrydate > '20140101'

and SQL makes an implicit conversion of '20140101' to a date (but it IS important to use a date format that MS SQL regards as unambiguous!!)

Presumably you do not have a similar issue in Oracle? otherwise I assume I would see more code examples along the lines of

AND a.appentrydate > todate('20140101', 'yyyymmdd')

(assuming that this is an equivalent of a "todate" function that converts a string-date into a date datatype)