update from multiple tables

I can't get this to work in Oracle 10g. I am wondering if it can even be done via sql. I may have to write a cursor to do this. SQL Navigator is already showing a message saying "Cursor must be opened". Please help me out. I cannot create a view and get this done, has to be a cursor or sql.

UPDATE TableA A
SET    A.eff_dt = (
                   SELECT  B.open_dt 
                   FROM    TableB B
                   WHERE   A.acct_id = B.acct_id)
WHERE A.acct_id = (
                   SELECT  C.acct_id
                   FROM    TableC C
                   WHERE   A.acct_id = C.acct_id
                   AND     A.eff_dt > (C.eff_dt -1)
                   )

-- shakti mann, February 17, 2011

Answers

You're setting single column values to the results of queries that may return more than one row. I don't see why that would work. The final WHERE clause, for example, would conventionally be "WHERE column_name IN (subquery)" not "WHERE column_name = (subquery)". The things on opposite sides of an equals sign have to be more or less of the same type. It doesn't make sense to tell Oracle that a column that can hold a single number will now be holding 78 sets of three items, for example.

Oracle is giving you a pretty confusing error message, but that's probably because the developers never expected anyone to try what you're trying.

-- Philip Greenspun, February 17, 2011

I don't have much Oracle experience, but I think this would do what you're trying to do in SQL Server, perhaps you could adapt it for your use?

UPDATE A
SET A.eff_dt = B.open_dt
FROM TableA A
INNER JOIN TableB B ON A.acct_id = B.acct_id
INNER JOIN TableC C ON A.acct_id = C.acct_id AND A.eff_dt > (C.eff_dt - 1)

-- Joshua Levinson, April 12, 2011