update column in table with select from same table

[ssmith]

I want to update the column in a table using a select from the same table.
This is the sql I am trying:
update alpha_transactions a
set a.purch_datetime=(select to_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year||' '||lpad(hours,2,'0')||':'||lpad(minutes,2,'0'),'DD/MM/YYYY HH24:MI')
FROM ALPHA_TRANSACTIONS b
where b.purch_datetime is null
and a.txn_key=b.txn_key)
/
and it doens't work. It sets the column purch_datetime to null.
The
(select to_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year||' '||lpad(hours,2,'0')||':'||lpad(minutes,2,'0'),'DD/MM/YYYY HH24:MI')
FROM ALPHA_TRANSACTIONS b
where b.purch_datetime is null
does bring back the correct date but when I add the update and where a.txn_key=b.txn_key it doesn't work. What is the correct syntax? Thanks.

[jmodic]

can you explain what are you trying to do? Is TXN_KEY primary key (or unique) in this table?

IF TXN_KEY is PK you don't need any additional select, just do:

update alpha_transactions
set purch_datetime= to_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year||' '||lpad(hours,2,'0')||':'||lpad(minutes,2,'0'),'DD/MM/YYYY HH24:MI')
where....

[ssmith]

Thanks.