|Notify me of new responses|
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
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