Results 1 to 10 of 10

Thread: [RESOLVED] Update a field in multiple tables

  1. #1

    Thread Starter
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Resolved [RESOLVED] Update a field in multiple tables

    Hey all,
    We have a database with a number of tables that have a field that needs updating. The field name is the same across all tables and the value we need to update to will also be the same across all tables.

    I have the following statement that retrieves all of the tables with said field, but I'd like to know how I can update the fields as well...
    Code:
    SELECT      t.name AS 'TableName'
    FROM        sys.columns c
    JOIN        sys.tables  t   ON c.object_id = t.object_id
    WHERE       c.name = 'UpdatePath'
    ORDER BY    TableName
    I would like to do something like this (which does not work)...
    Code:
    UPDATE TableName SET UpdatePath = 'update'
    where TableName in (
    	SELECT      t.name AS 'TableName'
    	FROM        sys.columns c
    	JOIN        sys.tables  t   ON c.object_id = t.object_id
    	WHERE       c.name = 'UpdatePath'
    	ORDER BY    TableName)
    Does anyone have any idea about how to do this?
    Thanks
    Kevin
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Update a field in multiple tables

    I haven't tried but I guess something can be done with dynamic SQL.
    You essentially do a Loop on all the tables to get their names, construct the Dynamic SQL Query and have a change of the tablename on each iteration.
    Unless something can be done with straight T-sql which I'm not aware of.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Update a field in multiple tables

    Something similar to this at example on the second answer without the alter table.
    Well you need to count your tables to put it on the while variable but you get the idea.
    https://stackoverflow.com/questions/...through-tables
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4

    Thread Starter
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Re: Update a field in multiple tables

    Thanks sapator.
    The examples introduce a bunch of new concepts to me, but hey, it's only code. I can probably make something work from it.
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  5. #5
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Update a field in multiple tables

    It is not that hard if you have basic SQL knowledge.
    Just be careful with the creation of the string as sometimes it's tricky.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    Member
    Join Date
    Jul 2019
    Location
    Ahmedabad
    Posts
    57

    Re: Update a field in multiple tables

    Hello,@kebo
    Please try this query, To Update a field in multiple tables

    Code:
    Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' '
    From INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME = 'CreatedDateTime'
    I hope this query will be useful to you.
    Thank you.
    < advertising removed by moderator >

  7. #7

    Thread Starter
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Re: Update a field in multiple tables

    Quote Originally Posted by Sherin View Post
    Hello,@kebo
    Please try this query, To Update a field in multiple tables

    Code:
    Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' '
    From INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME = 'CreatedDateTime'
    I hope this query will be useful to you.
    Thank you.
    Thank for that Sherin, but your sql will only return a table with all of the update commands (one record for each table).

    How do I now get the server to process each record and actually perform the update?
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Update a field in multiple tables

    Try this - in a query window or a stored procedure if you need to use it regularly

    Code:
    Declare @SQLStatements Table (SqlText nvarchar(max))
    Declare @SQLText nvarchar(max)
    
    Insert into @SQLStatements Select 'Update'...and the rest of Sherin's select...
    
    While Exists(Select * From @SQLStatements)
    Begin
         Set @SQLText=(Select Min(SQLText) From @SQLStatements)
         Exec (@SQLText)
         Delete From @SQLStatements Where SQLText=@SQLText
    End

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9
    Fanatic Member
    Join Date
    Jun 2019
    Posts
    557

    Re: Update a field in multiple tables

    Why not save list of the queries in separate table? When you add or remove table with the field to be updated, then updated your queries in the "queries" table. In your application you can cache the queries and just execute without messing with system tables each time.

    If there is requirement to create new "table with field to update" each few seconds or minutes, then it is different story and pre-caching will not work well, but then there is something wrong in the application and database design.

  10. #10

    Thread Starter
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Re: Update a field in multiple tables

    Quote Originally Posted by szlamany View Post
    Try this - in a query window or a stored procedure if you need to use it regularly
    Thank you. It worked great. For anyone else that needs it, the complete sql looks like this...
    Code:
    Declare @SQLStatements Table (SqlText nvarchar(max))
    Declare @SQLText nvarchar(max)
    
    Insert into @SQLStatements Select 'UPDATE ' + TABLE_NAME + ' SET FIELD_TO_UPDATE= ''NEW_VALUE'' '
                               From INFORMATION_SCHEMA.COLUMNS 
                               WHERE COLUMN_NAME = 'FIELD_TO_UPDATE'
    
    While Exists(Select * From @SQLStatements)
    Begin
         Set @SQLText=(Select Min(SQLText) From @SQLStatements)
         Exec (@SQLText)
         Delete From @SQLStatements Where SQLText=@SQLText
    End
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width