Performing An SQL Update With Subqueries For Column Names

This is a really conviluted piece of SQL, I know, but basically one of the data entry monkeys where I work screwed up big-time, and the copy has gotten deep into our system.

There is a rather large number of tables that need to have a value changed from VAL1 to VAL2, if another value is equal to VAL3. The problem is that I don't know all of the tables where this is the case, and there is strict column naming policy that means that all tables have unique column names.

I wrote the following SQL to attempt to do this update, but it doesn't work:

UPDATE 
 (SELECT DISTINCT TABLE_NAME AS tbTableName 
  FROM ALL_TAB_COLUMNS 
  WHERE COLUMN_NAME LIKE '%MAJR%')
SET 
 (SELECT COLUMN_NAME AS tbColumnName 
  FROM tbTableName 
  WHERE COLUMN_NAME LIKE '%MAJR%') = 'VAL2'
WHERE 
 (SELECT COLUMN_NAME AS tbColumnNameWhere 
  FROM tbTableName 
  WHERE COLUMN_NAME LIKE '%PROGRAM%') = 'VAL3'
 AND tbColumnName = 'VAL1';

But yeah, this falls over with the error: invalid 开发者_开发百科user.table.column, table.column, or column specification 01747. 00000 - "invalid user.table.column, table.column, or column specification"

Any help would be appreciated.

You can't do subqueries like this, unfortunately. Your best bet is to use PHP or whatever your primary scripting language is and query the database to build all the SQL statements for you.