0

I've a column "pid" and a lot of table on each database , then I want to update all table from every database with column "pid" and set 'pid' = 5 where 'pid' = 3 and set 'pid' = 6 where 'pid = '7' on 1 query .

I've find post like this and try to apply it:

Select 'UPDATE ' + TABLE_NAME + ' SET pid = ''5'' '
From INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'pid'

Without condition for try it , then MYSQL just return me a select with value 0 .

'UPDATE ' + TABLE_NAME + ' SET pid = ''5'' '
                                           0

Need a little help and explain for make this query work and understand how its work .

Really thanks everyone :)

Kanzaki
  • 1
  • 2
  • you need dynamic SQL, so duplicate of [How to select from MySQL where Table name is Variable](https://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable) or about a hundred others – underscore_d Nov 14 '17 at 14:09
  • Thanks its maybe another way for do what i want , then i've guess its possible to do this with 1 request for be exec in php (query) . – Kanzaki Nov 14 '17 at 14:12
  • It's not really "another" way; it's **the** way. The alternative is that you just `select` strings to generate the commands you want, as above, then copy and paste and run those strings in the query editor... which is just a manual way of doing what the dynamic SQL would be doing for you anyway. – underscore_d Nov 14 '17 at 14:13
  • Thanks you , then SQL dynamic work perctly on Mysql 5.7 ? – Kanzaki Nov 14 '17 at 14:37

1 Answers1

-1
UPDATE table_name
SET pid = 5
WHERE pid = 3;

It should be implemented like this. You simply tell to update the table named table_name to set pid as '5' wherever it finds it as '3'.

Read also here. ;)

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Red fx
  • 1,071
  • 2
  • 12
  • 26
  • Its not really what i want , i've actually 4 table with column 'pid' and i want with 1 request update every table with column 'pid' and set to 5 if its already 3 and set 6 if its already 5 and every table on my all database . – Kanzaki Nov 14 '17 at 11:51
  • I am not sure if it is possible but try it with adding a comma and launching the statement like: Update table1,table2,table3 SET ... – Red fx Nov 14 '17 at 11:53
  • Forget what I've just said, just read here : https://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 – Red fx Nov 14 '17 at 11:55
  • UPDATE `table1` SET pid= CASE WHEN `pid` = 3 THEN 5 WHEN `pid` = 7 THEN 6 ELSE pid END WHERE `pid` IN(3,7); I must do it for every table on every database with column 'pid' then i just want impact every table with pid column on single request – Kanzaki Nov 14 '17 at 11:57
  • This is in case when i know every table with this name column , so i have different database with this column and a lot of table , and every database havent same table , i want i request to will uptade every table with 'pid' column with a condition on every database without know table name in advance . (and i'm on Mysql , not SQL server :s ) – Kanzaki Nov 14 '17 at 12:02
  • They use the same language which is SQL, it doesn't seem possible to me if not writing a function as on the link I pasted in my previous comment. Sorry I can't help you any further. – Red fx Nov 15 '17 at 08:13