如何更新item列中仅出现一次的条目对应的id值？

First, let's break down your table data to spot which items only appear once:

Looking at this, copy, book, and ball each show up exactly once—these are the rows we need to target for updating the id column.

Step 1: Confirm Unique Items #

First, run this query to double-check which items are unique:

SELECT item
FROM Table1
GROUP BY item
HAVING COUNT(*) = 1;

This will return:

item
-----
copy
book
ball

Step 2: Update the ID Values #

Since you didn’t specify exactly what to change the IDs to, here are two common approaches:

Option 1: Set a Single Consistent ID

If you want all unique items to share the same new ID (e.g., 40), use this query:

UPDATE Table1
SET id = 40 -- Replace with your desired ID
WHERE item IN (
    SELECT item
    FROM Table1
    GROUP BY item
    HAVING COUNT(*) = 1
);

Option 2: Assign Distinct IDs to Each Unique Item

If each unique item needs its own unique ID, use a CASE statement:

UPDATE Table1
SET id = CASE item
    WHEN 'copy' THEN 40
    WHEN 'book' THEN 50
    WHEN 'ball' THEN 60
    ELSE id -- Leave other items' IDs unchanged
END
WHERE item IN (
    SELECT item
    FROM Table1
    GROUP BY item
    HAVING COUNT(*) = 1
);

Example Result #

Using Option 1, your updated table would look like this:

Just tweak the ID values in the queries to match your specific requirements!

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