How to copy data from one table to another using one by one row-CodePudding

I'm trying to copy the data from the Player table to the User table.

Here's my Player table:

Name     Age  City
---------------------
pavan    27   Delhi
Kishor   29   Delhi
pavan    30   Delhi

I want to insert this data into the User table which has these columns:

Name    Age    Active

Now I want that there should be only one active name.

Rest all duplicate entries should be marked inactive (active = 0)

I tried this query, but it didn't work:

INSERT INTO User (name, age, active)
    SELECT 
        name, age,
        CASE 
            WHEN EXISTS(SELECT 1 FROM User u WHERE u.name = name) 
                THEN 0 
                ELSE 1 
        END

Thanks in advance.

CodePudding user response：

I would use a CTE here first to make the selection of which name record is set to active more deterministic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT NULL)) rn
    FROM Player
)

INSERT INTO [User] (name, age, active)
SELECT name, age, CASE WHEN rn = 1 THEN 1 ELSE 0 END
FROM cte;