如何更新Account表中Price_details列末尾数字递增1的行？

针对不同数据库，提供对应的SQL更新语句，核心逻辑是拆分字符串前缀与末尾数字，将数字加1后重新拼接。

MySQL/MariaDB #

UPDATE Account
SET Price_details = CONCAT(
    -- 提取倒数第二个'-'之前的前缀部分
    SUBSTRING_INDEX(Price_details, '-', LENGTH(Price_details) - LENGTH(REPLACE(Price_details, '-', '')) - 1),
    '-',
    -- 提取末尾数字并加1
    CAST(REGEXP_SUBSTR(Price_details, '[0-9]+$') AS UNSIGNED) + 1
)
-- 仅筛选以"-数字"结尾的行
WHERE Price_details REGEXP '-[0-9]+$';

验证语句（先测再更） #

SELECT 
    Price_details,
    CONCAT(
        SUBSTRING_INDEX(Price_details, '-', LENGTH(Price_details) - LENGTH(REPLACE(Price_details, '-', '')) - 1),
        '-',
        CAST(REGEXP_SUBSTR(Price_details, '[0-9]+$') AS UNSIGNED) + 1
    ) AS New_Price_details
FROM Account
WHERE Price_details REGEXP '-[0-9]+$';

SQL Server #

UPDATE Account
SET Price_details = CONCAT(
    -- 提取前缀（去掉末尾的"-数字"）
    SUBSTRING(Price_details, 1, LEN(Price_details) - PATINDEX('%[0-9]%', REVERSE(Price_details))),
    -- 提取末尾数字并加1
    TRY_CAST(SUBSTRING(Price_details, LEN(Price_details) - PATINDEX('%[0-9]%', REVERSE(Price_details)) + 1, LEN(Price_details)) AS INT) + 1
)
WHERE 
    Price_details LIKE '%-[0-9]%'
    -- 确保末尾是"-数字"格式
    AND PATINDEX('%-[0-9]%', REVERSE(Price_details)) = 1;

验证语句（先测再更） #

SELECT 
    Price_details,
    CONCAT(
        SUBSTRING(Price_details, 1, LEN(Price_details) - PATINDEX('%[0-9]%', REVERSE(Price_details))),
        TRY_CAST(SUBSTRING(Price_details, LEN(Price_details) - PATINDEX('%[0-9]%', REVERSE(Price_details)) + 1, LEN(Price_details)) AS INT) + 1
    ) AS New_Price_details
FROM Account
WHERE 
    Price_details LIKE '%-[0-9]%'
    AND PATINDEX('%-[0-9]%', REVERSE(Price_details)) = 1;

PostgreSQL #

UPDATE Account
SET Price_details = CONCAT(
    -- 去掉末尾的"-数字"得到前缀
    regexp_replace(Price_details, '-[0-9]+$', ''),
    '-',
    -- 提取末尾数字并加1
    (substring(Price_details FROM '-([0-9]+)$')::INT) + 1
)
-- 仅筛选以"-数字"结尾的行
WHERE Price_details ~ '-[0-9]+$';

验证语句（先测再更） #

SELECT 
    Price_details,
    CONCAT(
        regexp_replace(Price_details, '-[0-9]+$', ''),
        '-',
        (substring(Price_details FROM '-([0-9]+)$')::INT) + 1
    ) AS New_Price_details
FROM Account
WHERE Price_details ~ '-[0-9]+$';

注意事项 #

• 先验证再更新：生产环境务必先执行SELECT验证结果，确认无误后再执行UPDATE
• 事务保护：建议在事务中执行更新，若结果不符合预期可回滚
• 特殊值处理：WHERE条件已过滤不符合格式的行，避免因非目标格式值导致转换错误

内容的提问来源于stack exchange，提问作者FirasMsw