Update Statement with Table Joins and Where Clause
684479Aug 21 2009 — edited Aug 21 2009Hi, I have MS SQL background and I am trying to execute an update statement in Oracle with table joins. However, the below syntax does not work though I believe it works for MS SQL.
Basically the base table is to be joined to a trended master table loaded with monthly snapshots, an account will only have one entry for any given monthly date. The where clause is to limit to accounts within a certain interest rate range.
The first approach returns ORA-00933 SQL command not properly ended, and the second approach returns ORA-01427 single row sub-query returns more than one row. Can anyone help?? Thanks in advance!
1:
update PenaltyAll
set a.indicator=month(b.date)
from PenaltyAll a inner join Master b on a.acctno=b.accountnumber
where a.monthend='01/31/2009' and b.date='12/31/2008' and b.apr<20
2:
update PenaltyAll
set indicator =
(select to_char(b.date, 'MM')
from PenaltyAll a inner join Master b on a.acctno=b.accountnumber
where to_char(a.monthend,'mm/dd/yyyy') ='01/31/2009'
and to_char(b.date,'mm/dd/yyyy') ='12/31/2008'
and b.apr<20)
Edited by: sqlrookie on Aug 21, 2009 7:04 AM