March 26, 2008 at 2:24 am
I want to update over 700 tables. I don't know how to write a SQL query to update tables without hardcoding the table name.
Can anyone help me.
March 26, 2008 at 10:39 am
You can write a query against the catalog tables (tab, in this example) that builds an SQL script to do the updates. You have to parse the information you need to update into your query against the catalog, and spool the output to a file (usually ending with .sql), and then execute the .sql file you spooled.
I am not currently in a position to give you a good example and have oracle instances waiting for me; but, this is the technique I would take initially.
Good luck!
David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
March 26, 2008 at 4:48 pm
At least I didn't understood where (Oracle or MS SQL) you have to do these updates and what kind of updates... OK assuming this is almost Oracle forum 😉 you have to use old trick to "generate SQL using SQL" as already suggested.
There is only one view (actually synonym for view) you have to remember only one name - DICT! And from that you can find all other data dictionary views. Another option of course is to use Oracle docs and specifically Reference manual in http://tahiti.oracle.com
SQL> desc dict
Name Null? Type
----------------------------------------- -------- --------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> select * from dict where table_name like 'DICT%';
TABLE_NAME
------------------------------
COMMENTS
------------------------------------------------------------
DICTIONARY
Description of data dictionary tables and views
DICT_COLUMNS
Description of columns in data dictionary tables and views
DICT
Synonym for DICTIONARY
Gints Plivna
Gints Plivna
http://www.gplivna.eu
March 27, 2008 at 4:25 am
If you are doing in pl/sql (its the Oracle forum) then you can use execute immediate
Do something like
for v_tab in namecursor
loop
execute immediate('update table '||v_tab.name||' set colum=''newvalue''');
end loop;
where namecursor is a cursor looping over the tablenames.
April 7, 2008 at 7:50 am
ireshasun (3/26/2008)
I want to update over 700 tables. I don't know how to write a SQL query to update tables without hardcoding the table name.Can anyone help me.
Hi. In case you have not gotten what you need already you can use something like this:
SELECT IDENTITY(INT, 1, 1) AS tblid, NAME
INTO #ListOfTables
FROM sys.objects
WHERE TYPE = 'U'
SELECT * FROM #ListOfTables
DECLARE @ObjectID INT
DECLARE @ObjectName VARCHAR(255)
DECLARE @TotalObjects INT
DECLARE @CmdString VARCHAR(2000)
SELECT @TotalObjects = MAX(tblid) FROM #ListOfTables
SELECT @ObjectID = MIN(tblid) FROM #ListOfTables
WHILE @TotalObjects >= @ObjectID
BEGIN
SELECT @ObjectName = NAME FROM #ListOfTables WHERE tblid = @ObjectID
SELECT @ObjectID = @ObjectID + 1
SET @CmdString =
'UPDATE ' + @ObjectName +
' SET list of fields with values'
EXECUTE (@CmdString)
END
DROP TABLE #ListOfTables
It wil iterate through all the user-defined tables in the database and perform the update you specify. The specific code you use in the @CmdString variable will depend on what exactly you want to do. You can limit the list of tables with additional filters in the Where clause. Look up sys.objects in BOL for additional fields that can help with the filtering.
HTH
Don
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply