SQL help needed

[djneils98]

So I have a simple table called 'scores', for high scores in a game, with 3 fields - id(int pk) , name (text) and score (int)

I want to add new scores to the table only if the name doesn't already exist or the name exists but the current score is lower than the new score (and then to replace the current name and score) - basically so no one name has more than one score in the table (and the highest ever score at that)

Can I do this just in one SQL statement or do I need to do it in multiple steps in code (I'm using node.js) e.g. check if the name exists, look at the score, etc etc

thanks in advance

[nickd01]

You'd likely need a stored procedure to cater for the if/else condition.

The logic could be to try the two updates first, if they affect no rows (Checking @@rowcount for each statement) then go on to do the inserrt

[djneils98]

nickd01 said:

You'd likely need a stored procedure to cater for the if/else condition.

The logic could be to try the two updates first, if they affect no rows (Checking @@rowcount for each statement) then go on to do the inserrt

thanks

[playalistic]

Which database is it?

[djneils98]

playalistic said:

Which database is it?

sqlite3

[Alex]

if exists (select * from scores where name = @name)
update scores set score = @score where name = @name and score < @score
else
insert into scores (name, score) values (@name, @score)

[mikef]

Check out UPSERT

[djneils98]

still not quite got it working - thanks for everyones help so far

the table is actually a table of times in seconds and names. lowest time is best. not scores

this is what I'm trying as I don't think sqlite3 does IF statements

select case when exists (select * from times where name = "neil") then update times set time = 7 where name = "neil" and time > 7 else insert into times (name, time) values ("neil", 7) end;


getting an sql error that says { [Error: SQLITE_ERROR: near "update": syntax error] errno: 1, code: 'SQLITE_ERROR' }

[mikef]

djneils98 said:

Can I do this just in one SQL statement

Yes, you can. In database parlance this is known as an "upsert statement". It's non-standard SQL so syntax varies by platform. SQLite follows the postgres syntax of INSERT … ON CONFLICT …

For instance and assuming you have declared [name] as UNIQUE

INSERT INTO times(name, time)
VALUES ('DJNeils', 44.5)
ON CONFLICT (name) DO UPDATE
SET time = CASE WHEN excluded.time < time THEN excluded.time ELSE time END
WHERE name = excluded.name
;

[djneils98]

Thanks will give that a go

[djneils98]

mikef said:

Yes, you can. In database parlance this is known as an "upsert statement". It's non-standard SQL so syntax varies by platform. SQLite follows the postgres syntax of INSERT … ON CONFLICT …

For instance and assuming you have declared [name] as UNIQUE

INSERT INTO times(name, time)
VALUES ('DJNeils', 44.5)
ON CONFLICT (name) DO UPDATE
SET time = CASE WHEN excluded.time < time THEN excluded.time ELSE time END
WHERE name = excluded.name
;

that's a winner!! many thanks and happy new year!