如何用同表其他列组合数据更新ID列？附示例需求

Hey there! Let's figure out how to update your ID column using the existing data in your table. Based on your example, you want to combine DOB, the first 3 uppercase characters of FirstName, first 3 uppercase characters of LastName, and Gender into a new ID (like 19560809JOHDOEM). Here's how to do this across the most common database systems:

MySQL/MariaDB #

Use the CONCAT() function to string together the values, UPPER() to convert names to uppercase, and LEFT() to grab the first 3 characters of the names:

UPDATE your_table_name
SET ID = CONCAT(DOB, UPPER(LEFT(FirstName, 3)), UPPER(LEFT(LastName, 3)), Gender);

• Replace your_table_name with the actual name of your table.
• If FirstName or LastName has fewer than 3 characters, LEFT() will just return all available characters (e.g., "Jo" becomes "JO").

SQL Server #

You can use CONCAT() (available in SQL Server 2012+) which handles different data types automatically:

UPDATE your_table_name
SET ID = CONCAT(DOB, UPPER(LEFT(FirstName, 3)), UPPER(LEFT(LastName, 3)), Gender);

For older SQL Server versions that don't support CONCAT(), cast numeric/date types to strings first and use + to concatenate:

UPDATE your_table_name
SET ID = CAST(DOB AS VARCHAR(8)) + UPPER(LEFT(FirstName, 3)) + UPPER(LEFT(LastName, 3)) + Gender;

PostgreSQL #

PostgreSQL uses || for string concatenation. If your DOB is a date type, use TO_CHAR() to format it as YYYYMMDD first:

-- If DOB is already a string (like '19560809')
UPDATE your_table_name
SET ID = DOB || UPPER(LEFT(FirstName, 3)) || UPPER(LEFT(LastName, 3)) || Gender;

-- If DOB is a date type
UPDATE your_table_name
SET ID = TO_CHAR(DOB, 'YYYYMMDD') || UPPER(LEFT(FirstName, 3)) || UPPER(LEFT(LastName, 3)) || Gender;

Important Notes #

• Test first before updating! Run a SELECT query to verify the new IDs look correct:

  -- Example for MySQL
  SELECT 
    CONCAT(DOB, UPPER(LEFT(FirstName, 3)), UPPER(LEFT(LastName, 3)), Gender) AS New_ID,
    *
  FROM your_table_name;
  

• Make sure your ID column has enough length to fit the new value. The example ID is 15 characters (8 from DOB + 3 + 3 + 1), so ensure the column's length is at least 15 (e.g., VARCHAR(20)).
• If any of the columns (FirstName, LastName, etc.) might have NULL values, use COALESCE() to avoid getting a NULL ID. For example: COALESCE(UPPER(LEFT(FirstName, 3)), '') to replace NULL with an empty string.

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