如何用单条SQL语句基于ID更新同表中GROUPNAME与GROUPNAME2列值？

Hey there! Sounds like you need to batch update two columns based on the ID value for your 2000-record table using a single SQL statement—totally doable. Let's break this down step by step.

Core Idea #

We can leverage string concatenation to generate the values for GROUPNAME and GROUPNAME2 directly from the ID column, all in one UPDATE query.

Generic SQL Statement #

This works across most databases (with minor adjustments for specific systems):

UPDATE your_table_name
SET 
    GROUPNAME = CONCAT(ID, '_ADMIN'),
    GROUPNAME2 = CONCAT(ID, '_group');

Replace your_table_name with the actual name of your table. This query will update every row in the table, setting GROUPNAME to [ID]_ADMIN and GROUPNAME2 to [ID]_group exactly like your sample data shows.

Database-Specific Adjustments #

Some databases have different syntax for string concatenation—here's how to adjust if needed:

• MySQL/MariaDB: The generic CONCAT() function works out of the box, even if ID is a numeric type (it auto-converts to string).
• SQL Server: If using versions before 2012, cast ID to a string first; otherwise, CONCAT() handles type conversion automatically:

  -- Pre-2012 version
  UPDATE your_table_name
  SET 
      GROUPNAME = CAST(ID AS VARCHAR(20)) + '_ADMIN',
      GROUPNAME2 = CAST(ID AS VARCHAR(20)) + '_group';
  
  -- 2012+ version (simpler)
  UPDATE your_table_name
  SET 
      GROUPNAME = CONCAT(ID, '_ADMIN'),
      GROUPNAME2 = CONCAT(ID, '_group');
  

• PostgreSQL: Use the || concatenation operator or CONCAT():

  UPDATE your_table_name
  SET 
      GROUPNAME = ID || '_ADMIN',
      GROUPNAME2 = ID || '_group';
  

Pro Tips to Avoid Mistakes #

• Test first with SELECT: Before running the update, verify the output matches your expectations with a select query:

  SELECT 
      ID, 
      CONCAT(ID, '_ADMIN') AS expected_groupname,
      CONCAT(ID, '_group') AS expected_groupname2
  FROM your_table_name
  LIMIT 10; -- Check the first 10 rows to confirm
  

• Filter rows if needed: If you don't want to update every row, add a WHERE clause. For example, to only update IDs starting with 0106:

  UPDATE your_table_name
  SET 
      GROUPNAME = CONCAT(ID, '_ADMIN'),
      GROUPNAME2 = CONCAT(ID, '_group')
  WHERE ID LIKE '0106%';
  

• 2000 rows is no problem: This query will run quickly even for your 2000-record table—no performance concerns here.

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