代码之家 › 专栏 › 技术社区 › John James

在一个查询中更新并从同一个表中选择

sql-update join select mysql

John James · 技术社区 · 7 年前

我试图计算表1中的行数,并根据行数更新某一列。下面是我尝试过的查询,但得到一个arror,它说temp不是一个表。

UPDATE table1 AS t1
INNER JOIN table1 AS temp ON temp.id = t1.id
SET
t1.field1 = (CASE
    WHEN (SELECT COUNT(*) FROM temp WHERE temp.field1 = 1) < 100 THEN 1
    WHEN (SELECT COUNT(*) FROM temp WHERE temp.field1 = 2) < 100 THEN 2
    WHEN (SELECT COUNT(*) FROM temp WHERE temp.field1 = 3) < 100 THEN 3
    WHEN (SELECT COUNT(*) FROM temp WHERE temp.field1 = 4) < 100 THEN 4
    WHEN (SELECT COUNT(*) FROM temp WHERE temp.field1 = 5) < 100 THEN 5
END)
WHERE t1.id IN(100, 200, 300); --Example data

2 回复 | 直到 7 年前

Crutches 7 年前

有几件事:

我建议在你的情况下做一个临时数据表语句,然后加入该语句进行更新。
重新加入正在更新的表不起作用。
where子句中有语法错误。你不需要这个输入前等于号。

尝试:

DROP TABLE IF EXISTS temp_table1;
CREATE TEMPORARY TABLE temp_table1 AS
SELECT field1,count(*) as field_count FROM table1 group by field1;

UPDATE table1 AS t1
LEFT JOIN temp_table1 aa
  ON aa.field1= t1.field1
SET t1.field1 = (CASE 
  WHEN aa.field1 = 1 AND aa.field_count < 100 THEN 1 
  WHEN aa.field1 = 2 AND aa.field_count < 100 THEN 2 
  WHEN aa.field1 = 3 AND aa.field_count < 100 THEN 3 
  WHEN aa.field1 = 4 AND aa.field_count < 100 THEN 4 
  WHEN aa.field1 = 5 AND aa.field_count < 100 THEN 5 END)
WHERE t1.id IN (100, 200, 300);

Abhilash Ravindran C K 7 年前

以下查询将对您有所帮助,

UPDATE table1 as t1
SET
t1.field1 = (CASE         
    WHEN (select p.cnt from (SELECT COUNT(*) as cnt FROM table1 t2 WHERE t2.field1= 1) as p) < 100 THEN 1
    WHEN (select p.cnt from (SELECT COUNT(*) as cnt FROM table1 t2 WHERE t2.field1= 2) as p) < 100 THEN 2
    WHEN (select p.cnt from (SELECT COUNT(*) as cnt FROM table1 t2 WHERE t2.field1= 3) as p) < 100 THEN 3
    WHEN (select p.cnt from (SELECT COUNT(*) as cnt FROM table1 t2 WHERE t2.field1= 4) as p) < 100 THEN 4
    WHEN (select p.cnt from (SELECT COUNT(*) as cnt FROM table1 t2 WHERE t2.field1= 5) as p) < 100 THEN 5    
 END)
WHERE t1.id IN(100, 200, 300)