mysql - SQL 更新 : the row such that. ..？

Question

假设f是一个字符串函数，例如按0串联:f(0011)=00110。

假设mytable有作为索引的string列和price。

我想做类似以下伪代码的事情:

UPDATE mytable SET price(X)=0.5 FOR ALL THOSE ROWS X SUCH THAT
THE ROW Y SUCH THAT THE `string` OF Y equals f(the `string` FOR X)
HAS price(Y)=0

更一般地说，我如何引用

"the row such that [condition-holds-that-depends-on-the-current-row]"

在(我的)SQL 中？

Answer 1

设置演示

CREATE TABLE mytable 
( id         INT UNSIGNED NOT NULL PRIMARY KEY
, thestring  VARCHAR(32)
, price      DECIMAL(11,2)
) ENGINE=INNODB
;
INSERT INTO mytable (id, thestring, price) VALUES
 ( 1,'0011'   , 123.45)
,( 2,'00110'  ,  45.67)
,( 3,'001100' ,   0.00)   
,( 4,'10'     ,   4.44)
,( 5,'100'    ,   5.55)
,( 6,'1000'   ,   0.00)
;

编写一个查询来标识 mytable 中的 Y 行

SELECT y.*
  FROM `mytable` `y` 
 WHERE y.price = 0.0 
 ORDER BY y.id

向 mytable 添加联接以查找匹配的 X 行

SELECT x.id         AS x_id
     , x.thestring  AS x_thestring 
     , x.price      AS x_price
     , y.id         AS y_id 
     , y.thestring  AS y_thestring
     , y.price      AS y_price
  FROM mytable `y`
  JOIN mytable `x` 
    ON CONCAT(x.thestring,'0') = y.thestring 
 WHERE y.price = 0.0 
 ORDER BY y.id

将 SELECT 转换为 UPDATE。 (将 SELECT ... FROM 替换为 UPDATE，并在 WHERE 子句之前添加 SET 子句。

UPDATE mytable `y`
  JOIN mytable `x` 
    ON CONCAT(x.thestring,'0') = y.thestring
   SET x.price = 0.5
 WHERE y.price = 0.0 

只需将 CONCAT(x.thestring,'0') 替换为 f(x.thestring) 即可。

<小时/>

另一种选择是使用相关子查询。

首先，写一个SELECT

SELECT x.*
  FROM mytable `x`
 WHERE EXISTS ( SELECT 1
                  FROM mytable `y`
                 WHERE y.thestring = f(x.thestring)
                   AND y.price = 0.00
              )

然后将其转换为更新。将 SELECT ... FROM 替换为 UPDATE 关键字，并在 WHERE 子句之前添加 SET 子句。

UPDATE mytable `x`
   SET x.price = 0.5
 WHERE EXISTS ( SELECT 1
                  FROM mytable `y`
                 WHERE y.thestring = f(x.thestring)
                   AND y.price = 0.00
              )