0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pavan kumar
  • 89
  • 1
  • 2
  • 10
  • `there should be only one unique active name` ... what is the logic determining whose name gets assigned to active? – Tim Biegeleisen Aug 04 '22 at 09:14
  • the second pavan should be marked as inactive because it's the duplicate one in the table. – pavan kumar Aug 04 '22 at 09:16
  • First, `USER` is a reserved word, so you cannot use it as table name in this way. Second, you missed up a `FROM` clause in your `SELECT`. It would be `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 FROM Player`. Rename your `User` table and all should work – Alexey Aug 04 '22 at 09:19
  • 2
    Which one is the "second" Pavan? Is the second one always the elder? – Thom A Aug 04 '22 at 09:22

1 Answers1

3

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360