2

I am trying to change a specific value, which has no connection to it's column name or table.

For example it can appear in:

column `name` in the table `users`

And also in:

column `hi` in the table `jokes`

There are many more unknown locations. How can I run a loop on all the sql data in my database to change it?

I'm using PHPMyAdmin

Novak
  • 2,760
  • 9
  • 42
  • 63
  • How many tables are you talking about? You either need to write an update for every field in every table, or write code that writes code *(Dynamic SQL)* to do the update. You end up in the same place, both run updates on all fields in all tables, but the Dynamic SQL option is *not* trivial. You may be better off with copy and paste to write a whole bunch of queries that do nearly identical things to different tables. – MatBailie Jun 22 '12 at 12:33
  • @Dems really? Imagine you needed to create a bunch of strings. It will consist of static strings and data that comes from a database. You'll then use these string to be passed as arguments to something else. You think the best solution is copy paste? – Conrad Frix Jun 22 '12 at 12:59
  • *"I am trying to change a specific value, which has no connection to it's column name or table."* Oof. There is a problem here, but it has to do with the second part of the sentence. – Jason Jun 22 '12 at 13:18
  • 1
    @ConradFrix - My understanding was that the data was static (`specific value` from the question). If that's the case it *feels* like large number of simple and similar queries. If that's wrong, then Copy&Paste may not be as straight forward a solution. – MatBailie Jun 22 '12 at 13:23

2 Answers2

1

As you are saying PHPMyAdmin I suppose you are using MySQL.

See:

There's also a PHP script to do this at:

Community
  • 1
  • 1
MicSim
  • 26,265
  • 16
  • 90
  • 133
0

For SQL server 2000 : i wrote this script

-- full search and replace version sql server 2000
SET NOCOUNT ON

DECLARE @columnname varchar(255), @tablename varchar(255), @sql varchar(8000), @oldvalue varchar(1000), @newvalue varchar(1000)

SET @oldvalue = 'OldText' -- text to replace
SET @newvalue = 'NewText'  -- text to replace by

DECLARE table_cursor CURSOR FOR
select name from sysobjects where xtype='U' AND [name] LIKE 'MyTablePrefix_%' -- your table prefix
OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'Updating Table: ' + @tablename + '...'

DECLARE column_cursor CURSOR FOR
SELECT     syscolumns.name AS column_name
FROM         sysobjects INNER JOIN
                      syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
                      systypes ON syscolumns.xtype = systypes.xtype
WHERE     (sysobjects.xtype = 'U')
AND       systypes.[name] IN ('varchar','nvarchar','text','ntext')
AND       sysobjects.name =  @tablename
ORDER BY sysobjects.name, syscolumns.colid

OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @columnname

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'Updating Column: ' + @columnname + '...'

SET @SQL = 'UPDATE ' + @tablename
SET @SQL = @SQL + ' SET ' + @columnname + ' = Replace(CAST(' + @columnname + ' AS nvarchar(4000)),'''+ @oldvalue + ''',''' + @newvalue + ''')'
--PRINT @sql
EXECUTE(@SQL)

FETCH NEXT FROM column_cursor INTO @columnname
END

CLOSE column_cursor
DEALLOCATE column_cursor

FETCH NEXT FROM table_cursor
INTO @tablename

END
CLOSE table_cursor
DEALLOCATE table_cursor

Run it during the night as it take quite a while to run on medium to big databases...

ToXinE
  • 308
  • 2
  • 13
  • there is a script in that link for this stuff, you can check it out here https://github.com/massooti/db_fieldReplacer – Masoud Jan 03 '21 at 19:10