多表更新(MySQL) [英] Multi-table Update(MySQL)

问题描述

我对多表更新(MySQL)有疑问.考虑表t1和t2. t1的PKEY是'tid'，它是t2中的外键. t2中有一个字段"qtyt2"，该字段取决于表t1中一个名为"qtyt1"的字段.考虑以下SQL语句:

I have a question regarding multi-table update(MySQL). Consider table t1 and t2. The PKEY for t1 is 'tid' which is a foreign Key in t2. There is a field "qtyt2" in t2 which depends on a field called "qtyt1" in table t1. Consider the following SQL statement:

UPDATE t2,t1
   SET t2.qtyt2=IF(( t2.qtyt2- t1.qtyt1 )<0,0,( t2.qtyt2- t1.qtyt1 ) ),
       t1.qtyt1 ="Some value.."
 WHERE t2.tid="some value.."
   AND t2.tid=t1.tid

在此示例中，qtyt2依赖于qtyt1进行更新，后者本身也已更新.如果更新两行，则结果应返回2.

In this example qtyt2 depends on qtyt1 for update and the latter itself is updated.Now the result should return 2 if two rows are updated.

是否可以保证这些字段将按照它们在语句中出现的顺序进行更新(首先设置qtyt2，然后设置qtyt1)?

Is there a guarantee that the fields will be updated in the order in which they appear in the statement( first qtyt2 will be set and then qtyt1)?

是否可能先设置qtyt1，然后再设置qtyt2?

Is it possible that qtyt1 will be set first and then qtyt2?

语句中的表顺序是否重要(UPDATE t2，t1或UPDATE t1，t2)?

Is the order of tables in the statement important (UPDATE t2, t1 or UPDATE t1,t2)?

我发现，如果我写了"UPDATE t1，t2"，则只会更新t1，但是在将语句更改为"UPDATE t2，t1"后，一切正常.

I found that if I wrote "UPDATE t1,t2" then only t1 would get updated, but on changing the statement to "UPDATE t2,t1" everything worked correctly.

推荐答案

首先，建议您明确显示JOIN.第二，我认为您的病情有错别字，应该为WHERE t2.qtyt2='Some value..'.所以:

First off, it is always advisable to make your JOINs explicit. And 2nd, I think your condition has a typo and should be WHERE t2.qtyt2='Some value..'. So:

UPDATE t2 JOIN t1 ON (t2.tid=t1.tid)
SET t2.qtyt2= IF(( t2.qtyt2- t1.qtyt1 )<0, 0,( t2.qtyt2- t1.qtyt1 ) ), 
    t1.qtyt1 ="Some value.." 
WHERE t2.qtyt2="Some value..";

如果以上是您的意思，那么我认为应该发生的是SQL将在t2.qtyt2="Some value.."所在的行中找到行集，然后将更新t2.qtyt2，然后将所有t1.qtyt1设置为行设置)为某些值.." (而不是t2.qtyt2的新值).

If the above is what you mean, then I believe what should be happening is that SQL will find the row set where t2.qtyt2="Some value..", then it will update t2.qtyt2, and then it will set all t1.qtyt1 (in the row set) to "Some value.." (and not the new value of t2.qtyt2).

这篇关于多表更新(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！