PL/SQL中UPDATE语句拼接列与变量：按迭代器格式更新用户名求助

Hey there! Let's work through this username update issue. You're aiming for usernames like user1, user2, user3 but ended up with app2nd—that tells me your current logic is either pulling in unexpected text from existing usernames, or formatting the iterator as an ordinal suffix (like "2nd" instead of just "2") by mistake.

Here are a couple of solid approaches to get the result you want:

1. PL/SQL Loop with Numeric Counter #

If you prefer using a PL/SQL block for more control, this straightforward loop will iterate through your users and assign a sequential numeric suffix:

DECLARE
    v_seq_num NUMBER := 1; -- Initialize your starting counter
BEGIN
    -- Loop through users (adjust ORDER BY to prioritize which users get which numbers)
    FOR user_rec IN (SELECT user_id FROM your_users_table ORDER BY user_id) LOOP
        UPDATE your_users_table
        SET username = 'user' || v_seq_num -- Directly append the numeric counter
        WHERE user_id = user_rec.user_id;
        
        v_seq_num := v_seq_num + 1; -- Increment counter for next user
    END LOOP;
    COMMIT; -- Don't forget to commit the changes!
END;
/

2. SQL MERGE with ROW_NUMBER() (No PL/SQL Needed) #

For a more efficient, set-based approach (great for large datasets), use the MERGE statement combined with the ROW_NUMBER() window function to generate sequential numbers:

MERGE INTO your_users_table target
USING (
    -- Generate the new username with sequential number for each user
    SELECT 
        user_id,
        'user' || ROW_NUMBER() OVER (ORDER BY user_id) AS new_username
    FROM your_users_table
) source
ON (target.user_id = source.user_id)
WHEN MATCHED THEN
    UPDATE SET target.username = source.new_username;

COMMIT;

Why You Got "app2nd" #

Chances are your original code was doing one of these:

• Pulling the app prefix from existing usernames (maybe using SUBSTR on old usernames that started with app)
• Formatting the iterator as an ordinal (e.g., using TO_CHAR(rownum, 'th') which turns 2 into "2nd")

Double-check your old query for these patterns—swap out any ordinal formatting for plain numeric concatenation, and make sure you're explicitly setting the prefix to user instead of pulling from existing values.

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