CODEWITHSUNDEEP
sqldatabasesqlite
Arkie Mandziy
Arkie Mandziy

Reputation: 13

Updating SQL database column based on rank

I'm working on a program and one aspect is ranking each player based on their ELO score. I've been able to create a SQL query that can provide rankings as an output but I can't figure out how to make it actually update the database table. I am currently using SQLite

Current Database Table

Current Code:

SELECT
  name,
  elo,
  losses,
  matches,
  RANK() OVER(ORDER BY elo DESC)

FROM membersData

{Ideal Database after update]2

Any help would be greatly appreciated

Upvotes: 0

Views: 532

Answers (3)

Hiren Patel
Hiren Patel

Reputation: 317

-- Use a common table expression (CTE) to rank values
WITH RankedValues AS (
    SELECT 
        id,
        value,
        ROW_NUMBER() OVER (PARTITION BY value ORDER BY value) AS rank_value
    FROM 
        MyTable
)

-- Update the rank_column with the rank value from the CTE
UPDATE MyTable
SET rank_column = rv.rank_value
FROM MyTable mt
JOIN RankedValues rv ON mt.id = rv.id;

Upvotes: 0

nbk
nbk

Reputation: 49375

Assuming that name is unique, so that we can join

 UPDATE membersData m1
 INNER JOIN (
 SELECT
  name,
  RANK() OVER(ORDER BY elo DESC) newrank FROM membersData) m2 ON m1.name = m2.name

SET m1.rank = m2.newrank

Schema (SQLite v3.30)

CREATE TABLE membersData(
    name    TEXT   ,
    elo     DOUBLE,
    wins    INTEGER,
    losses  INTEGER,
    matches INTEGER,
    ranking INTEGER
);

INSERT INTO membersData (
                            ranking,
                            matches,
                            losses,
                            wins,
                            elo,
                            name
                        )
                        VALUES (
                            NULL,
                            4,
                            3,
                            1,
                            123123,
                            'James'
                        ),
                        (
                            NULL,
                            5,
                            4,
                            1,
                            123,
                            'Top'
                        ),
                        (
                            NULL,
                            5,
                            2,
                            3,
                            1000,
                            'harry'
                        ),
                        (
                            NULL,
                            5,
                            0,
                            5,
                            450,
                            'kai'
                        );

Query #1

UPDATE membersData 
SET ranking = (SELECT COUNT(*) + 1
                   FROM membersData md2
                   WHERE md2.elo > membersData.elo
                  );

There are no results to be displayed.

Query #2

SELECT * FROM membersData ORDEr BY ranking;
name elo wins losses matches ranking
James 123123 1 3 4 1
harry 1000 3 2 5 2
kai 450 5 0 5 3
Top 123 1 4 5 4

View on DB Fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method is a correlated subquery in the UPDATE:

UPDATE membersData md
    SET ranking = (SELECT COUNT(*) + 1
                   FROM membersData md2
                   WHERE md2.elo > md.elo
                  );

Upvotes: 0

Related Questions