我正在尝试使用"year_from"和"year_to"数据更新"model_name",但我有 6k 个随机model_id模型,所以我想更新与model_id匹配的模型名称。如果我删除"其中 model_id = 2"行,它会用最小值和最大值更新整个表。如何修改此查询?
UPDATE lc_vehicles2
SET model_name = concat(model_name, ' (', (
SELECT MIN(year_from)
FROM (SELECT * FROM lc_vehicles2) AS VEHICLES
WHERE model_id = 2),'-', (
SELECT MAX(year_to)
FROM (SELECT * FROM lc_vehicles2) AS VEHICLES
WHERE model_id = 2),')')
表结构:
brand_id | brand_name | model_id | model_name | year_from | year_to
502 | audi | 2288 | A6 | 1999.06 | 2006.01
145 | volvo | 2154 | S60 | 2006.06 | 2012.12
UPDATE lc_vehicles2 VH
INNER JOIN
(
SELECT MIN(year_from) MIN_YEAR,model_id FROM lc_vehicles2 GROUP BY model_id
)MIN_TABLE ON MIN_TABLE.model_id=VH.model_id
INNER JOIN
(
SELECT MAX(year_to) MAX_YEAR,model_id FROM lc_vehicles2 GROUP BY model_id
)MAX_TABLE ON MAX_TABLE.model_id=VH.model_id
SET model_name = CONCAT(VH.model_name,'(',MIN_TABLE.MIN_YEAR,'-',MAX_TABLE.MAX_YEAR,')')
尝试上面的查询。