Performing An SQL Update With Subqueries For Column Names

Question

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 are going to need to use dynamic sql

TrevDev
– TrevDev

2011-05-04 06:07:54 +00:00
Commented May 4, 2011 at 6:07 — TrevDev
– TrevDev, Commented May 4, 2011 at 6:07

David Fells · Accepted Answer · 2011-05-04 06:13:02Z

1

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.

answered May 4, 2011 at 6:13

David Fells

6,8081 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dwaine Bailey Over a year ago

Thanks heaps for that :) - I'm using SQR, so it shouldn't be too bad

Collectives™ on Stack Overflow

Performing An SQL Update With Subqueries For Column Names

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related