If delete_dt is a DATE, then don't try to set it ot a string value, such as '2014-05-06'. Use TO_DATE if you need to convert a string into a DATE.
However, that probably isn't what's cause the error you reported.
What are you trying to do? It's very hared to tell just by looking at code that doesn't do it.
The first line you posted
update table stg_ccd_AMIB P set delete_dt = '2014-05-06'
all by itself is a valid UPDATE statement. What does the rest of the code
((select acct,REC_NBR,row_NUMBER() over(partition by acct order by REC_NBR desc)as row_num
from stg_ccd_AMIB) A WHERE
a.ROW_NUM<=(select count(*) from stg_ccd_ibseq where acct=a.acct)) x where P.acct = X.acct
have to do with that first line?
If you want to use analytic functions, such as ROW_NUMBER, in an UPDATE statement, then you might want to use MERGE instead of UPDATE.
Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
Always say which version of Oracle you're using (for example, 22.214.171.124.0).
See the forum FAQ: https://forums.oracle.com/message/9362002#9362002