使用mysql中的单个查询更新所有表中的值 [英] update values in all tables using single query in mysql

问题描述

我想使用在所有列中都有效的某种条件，在MySql中使用单个查询更新多列.

I want to update multiple columns using a single query in MySql with some condition that is valid across all colums.

我大约有25个具有相同表结构的数据库.我需要使用 phpMyAdmin 的单个查询在所有数据库中为特定URL更新一个名为Status的字段.

I have around 25 databases with the same table structure in them. I need to update a field named Status across all databases for a particular url using a single query using phpMyAdmin.

现在我正在使用此查询. (我需要在所有这些url为'www.google.com'的数据库中，在table1中的status=2中设置status=2)

Right now I am using this query. (I need to set status=2 in table1 in all of these databases where the url is 'www.google.com')

UPDATE  `database1`.`table1` as p1,
    `database2`.`table1` as p2 ,
    `database3`.`table1` as p3
SET  p1.`STATUS` = 2,
    p2.`STATUS`= 2,
    p3.`STATUS`= 2 
WHERE p1.url='www.google.com' and
    p1.url=p2.url=p3.url

但是它不仅在url='www.google.com'中更新所有行中的数据.它还正在更新'www.yahoo.com'中的status=2.

But it is updating data in all rows not only url='www.google.com'. It is updating status=2 in 'www.yahoo.com' also.

请帮助我解决这个问题.

Please help me to solve this.

推荐答案

这就是您想要的:

UPDATE
    database1.table1 AS p1
    LEFT JOIN database2.table1 AS p2 USING (url)
    LEFT JOIN database3.table1 AS p3 USING (url)
SET
   p1.`STATUS` = 2,
   p2.`STATUS` = 2,
   p3.`STATUS` = 2
WHERE
    url = 'www.google.com';

，因为p1.url=p2.url=p3.url
您没有工作 多数民众赞成在评估为p1.url=(p2.url=p3.url) 其中(p2.url=p3.url) =>对于所有不匹配项均为FALSE， 然后我们以p1.url=FALSE

and your didn't work becouse of p1.url=p2.url=p3.url
thats evaluated as p1.url=(p2.url=p3.url) where (p2.url=p3.url) => FALSE for all that didn't match, and then we end up with p1.url=FALSE

比较p1.url=FALSE =>比较字符串和布尔值， 将字符串转换为整数，对于非数字字符串则为0， 并将布尔值FALSE转换为整数也得到0，所以p1.url=FALSE

comparing p1.url=FALSE => comparing a string with an boolean, converting the string to an integer gives 0 for non numeric string, and converting boolean FALSE to integer gives also 0, so p1.url=FALSE

所以p1.url=p2.url=p3.url与NOT p2.url=p3.url

因此您的代码将更新存在不等于所有p3.urls的p2.url的所有行

so your code update all rows where there exists a p2.url thats not equal to all p3.urls

这篇关于使用mysql中的单个查询更新所有表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！