My company has asked us to update tables using ROWID in SELECT statements instead of UPDATE as it takes less server resources (no idea if that’s true, just want they told us). This works fine for single table queries and even some simple two table joins. Recently I wrote a query that links tables across different databases and I defined the ROWID with the table I want it to be able to modify. If I entered a value in the table and hit commit, it seemed like it worked, but if I rerun the query or look for the update, it’s not there. Is this a limitation of ROWID or is there a solution? Thanks!
When you join tables, Toad may be trying to update one table using the rowid from another. You can turn on spool SQL to check that. (Main menu -> Database -> Spool SQL -> Spool to Screen).
It would probably be best to just use an update statement in this case. It should still be fast if you use primary key values to determine which rows to update.
does your query behave the same when you run it inside SQL*Plus?