按人员分组批量更新SSN及EmpID1字段的SQL实现问询

Alright, let's break this down into actionable steps that work with most SQL databases (I'll stick to standard syntax with notes for popular systems like PostgreSQL, SQL Server, and MySQL).

First, let's confirm the grouping logic: you're using first_name, last_name, address, and patient_number to uniquely identify each person — that makes sense as a composite key for grouping.

Step 1: Define Group-Level Values #

First, we'll create a common table expression (CTE) to calculate the unified SSN and EmpID1 for each person group.

Handling SSN: #

• If your existing SSN column has values that follow the XXX-XX-XXXX format (even if they're invalid for other reasons), we can just pick the minimum or maximum value from the group (either works for uniqueness).
• If all existing SSNs are invalid/non-conforming, we'll generate a unique, format-compliant SSN using the group's composite key to avoid duplicates.

Handling EmpID1: #

We'll pick a single EmpID1 value per group (I'll use MIN() here, but MAX() or even FIRST_VALUE() works too — just pick whichever makes sense for your data).

Here's the CTE:

WITH PersonGroups AS (
    SELECT
        first_name,
        last_name,
        address,
        patient_number,
        -- Option 1: Use existing valid-formatted SSN (min value)
        MIN(CASE WHEN ssn ~ '^\d{3}-\d{2}-\d{4}$' THEN ssn END) AS group_ssn,
        -- Option 2: Generate unique compliant SSN if no valid ones exist (PostgreSQL example)
        -- CONCAT(
        --     SUBSTRING(MD5(CONCAT(first_name, last_name, address, patient_number)) FROM 1 FOR 3),
        --     '-',
        --     SUBSTRING(MD5(CONCAT(first_name, last_name, address, patient_number)) FROM 4 FOR 2),
        --     '-',
        --     SUBSTRING(MD5(CONCAT(first_name, last_name, address, patient_number)) FROM 6 FOR 4)
        -- ) AS group_ssn,
        -- Pick one EmpID1 for the group
        MIN(empid1) AS group_empid1
    FROM your_table
    GROUP BY first_name, last_name, address, patient_number
)

Step 2: Update the Original Table #

Now we'll use this CTE to update every row in the original table with its group's unified values.

Standard SQL (PostgreSQL, SQL Server): #

UPDATE your_table t
SET
    ssn = pg.group_ssn,
    empid1 = pg.group_empid1
FROM PersonGroups pg
WHERE
    t.first_name = pg.first_name
    AND t.last_name = pg.last_name
    AND t.address = pg.address
    AND t.patient_number = pg.patient_number;

MySQL Syntax Adjustment: #

MySQL uses a slightly different JOIN syntax for updates:

UPDATE your_table t
JOIN PersonGroups pg
    ON t.first_name = pg.first_name
    AND t.last_name = pg.last_name
    AND t.address = pg.address
    AND t.patient_number = pg.patient_number
SET t.ssn = pg.group_ssn, t.empid1 = pg.group_empid1;

Step 3: Verify Before Committing #

Always validate your changes first before running the update! Use this query to check what the updated values will look like:

SELECT
    t.*,
    pg.group_ssn AS new_ssn,
    pg.group_empid1 AS new_empid1
FROM your_table t
JOIN PersonGroups pg
    ON t.first_name = pg.first_name
    AND t.last_name = pg.last_name
    AND t.address = pg.address
    AND t.patient_number = pg.patient_number;

Key Notes: #

• Double-check that your composite grouping columns (first_name, last_name, address, patient_number) truly uniquely identify each person — if there's overlap (e.g., two people with the same name/address but different patient numbers), adjust the grouping as needed.
• If you need guaranteed unique SSNs (no collisions even with the hash method), consider using a database sequence or auto-increment ID combined with random segments for the SSN. For example, in PostgreSQL:

  CONCAT(
      LPAD(nextval('ssn_sequence')::TEXT, 3, '0'),
      '-',
      LPAD(FLOOR(RANDOM() * 90 + 10)::TEXT, 2, '0'),
      '-',
      LPAD(FLOOR(RANDOM() * 9000 + 1000)::TEXT, 4, '0')
  ) AS group_ssn
  

• Don't forget to back up your table before running any mass updates — better safe than sorry!

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