请求编写SQL存储过程：按date_column排序将指定列从1开始递增更新

Hey there! Let's work through this problem—you need to update a numeric column to become a continuous sequence starting at 1, ordered by a date column, right? The original column has gaps (like 1,2,3,5,6,9...) and you want to fix that to be 1,2,3,4,5,6...

I'll share a robust stored procedure solution, with notes for major databases (SQL Server, MySQL) since syntax can vary a bit. The core idea is to generate a continuous row number ordered by your date column, then map that back to your table to update the target column.

Key Considerations First #

Before diving into code:

• You must have a unique identifier column (like a primary key) on your table. This ensures we can correctly match each row to its new sequence number without mistakes.
• Always test this on a backup or staging table first! Updates can't be undone easily if something goes wrong.
• We'll add parameter validation to avoid invalid inputs and reduce SQL injection risks.

SQL Server Stored Procedure #

This version uses a CTE (Common Table Expression) to generate the sequence, plus dynamic SQL to handle variable table/column names:

CREATE PROCEDURE UpdateToContinuousSequence
    @TableName NVARCHAR(128),
    @TargetColumn NVARCHAR(128),
    @DateColumn NVARCHAR(128),
    @PrimaryKeyColumn NVARCHAR(128) -- Pass your unique identifier column here
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate input columns exist
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @TargetColumn)
    BEGIN
        RAISERROR('Target column does not exist in the specified table.', 16, 1);
        RETURN;
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @DateColumn AND DATA_TYPE IN ('date', 'datetime', 'datetime2', 'smalldatetime'))
    BEGIN
        RAISERROR('Date column does not exist or is not a valid date type.', 16, 1);
        RETURN;
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @PrimaryKeyColumn)
    BEGIN
        RAISERROR('Primary key column does not exist in the specified table.', 16, 1);
        RETURN;
    END

    -- Build dynamic SQL with proper quoting to avoid injection
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'
    BEGIN TRANSACTION;
    BEGIN TRY
        WITH RankedRows AS (
            SELECT 
                ' + QUOTENAME(@PrimaryKeyColumn) + ',
                ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@DateColumn) + ') AS NewSequenceValue
            FROM ' + QUOTENAME(@TableName) + '
        )
        UPDATE t
        SET t.' + QUOTENAME(@TargetColumn) + ' = rr.NewSequenceValue
        FROM ' + QUOTENAME(@TableName) + ' t
        JOIN RankedRows rr ON t.' + QUOTENAME(@PrimaryKeyColumn) + ' = rr.' + QUOTENAME(@PrimaryKeyColumn) + ';

        COMMIT TRANSACTION;
        PRINT ''Update completed successfully! The target column now has a continuous sequence starting at 1.'';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        RAISERROR(''Update failed: %s'', 16, 1, ERROR_MESSAGE());
    END CATCH';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL;
END
GO

How to use it:

EXEC UpdateToContinuousSequence 
    @TableName = 'YourTableName',
    @TargetColumn = 'YourNumericColumn',
    @DateColumn = 'YourDateColumn',
    @PrimaryKeyColumn = 'YourPrimaryKeyColumn';

MySQL Stored Procedure #

MySQL handles dynamic SQL and transactions a bit differently, so here's an adapted version using a temporary table to store the sequence:

DELIMITER //

CREATE PROCEDURE UpdateToContinuousSequence(
    IN TableName VARCHAR(128),
    IN TargetColumn VARCHAR(128),
    IN DateColumn VARCHAR(128),
    IN PrimaryKeyColumn VARCHAR(128)
)
BEGIN
    DECLARE TargetExists INT DEFAULT 0;
    DECLARE DateExists INT DEFAULT 0;
    DECLARE PKExists INT DEFAULT 0;

    -- Validate target column exists
    SELECT COUNT(*) INTO TargetExists 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = TableName AND COLUMN_NAME = TargetColumn;

    IF TargetExists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Target column does not exist in the specified table.';
        RETURN;
    END IF;

    -- Validate date column exists and is a date type
    SELECT COUNT(*) INTO DateExists 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = TableName AND COLUMN_NAME = DateColumn AND DATA_TYPE IN ('date', 'datetime', 'timestamp');

    IF DateExists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Date column does not exist or is not a valid date type.';
        RETURN;
    END IF;

    -- Validate primary key column exists
    SELECT COUNT(*) INTO PKExists 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = TableName AND COLUMN_NAME = PrimaryKeyColumn;

    IF PKExists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Primary key column does not exist in the specified table.';
        RETURN;
    END IF;

    -- Start transaction
    START TRANSACTION;

    BEGIN TRY
        -- Create temp table with sequence numbers
        SET @CreateTempSQL = CONCAT(
            'CREATE TEMPORARY TABLE TempSequence AS SELECT ',
            PrimaryKeyColumn, ', ROW_NUMBER() OVER (ORDER BY ', DateColumn, ') AS NewSequenceValue FROM ', TableName, ' ORDER BY ', DateColumn
        );
        PREPARE stmt FROM @CreateTempSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- Update the original table
        SET @UpdateSQL = CONCAT(
            'UPDATE ', TableName, ' t JOIN TempSequence ts ON t.', PrimaryKeyColumn, ' = ts.', PrimaryKeyColumn, ' SET t.', TargetColumn, ' = ts.NewSequenceValue'
        );
        PREPARE stmt FROM @UpdateSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- Clean up temp table
        DROP TEMPORARY TABLE IF EXISTS TempSequence;

        COMMIT;
        SELECT 'Update completed successfully! The target column now has a continuous sequence starting at 1.' AS Result;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Update failed: ', ERROR_MESSAGE());
    END CATCH;
END //

DELIMITER ;

How to use it:

CALL UpdateToContinuousSequence('YourTableName', 'YourNumericColumn', 'YourDateColumn', 'YourPrimaryKeyColumn');

Final Notes #

• If your table doesn't have a single primary key, you can use a combination of columns that uniquely identify each row (adjust the JOIN logic accordingly).
• The ROW_NUMBER() function is standard in most modern databases (PostgreSQL works similar to SQL Server, just adjust the dynamic SQL syntax slightly).
• Always back up your data before running mass updates!

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