0

I want to get the id of a user for a given email, or if the email doesn't exist, I want it to return MAX(id)+1.

So for the example below, I want:

  • For email 'aaa@gmail.com', I want it to return 1
  • For email 'kkk@gmail.com', I want it to return 9

I made an attempt but I couldn't make it work the way I want.

DECLARE @USERS TABLE (id int, name varchar(50), email varchar(50));

INSERT INTO @USERS(id, name, email) VALUES
(1, 'john', 'aaa@gmail.com'),
(2, 'nick', 'bbb@gmail.com'),
(3, 'alex', 'ccc@gmail.com'),
(4, 'liam', 'ddd@gmail.com'),
(5, 'noah', 'eee@gmail.com'),
(6, 'oliver', 'fff@gmail.com'),
(7, 'james', 'ggg@gmail.com'),
(8, 'rob', 'hhh@gmail.com');

SELECT CASE WHEN (ISNULL(id, 0) = 0) THEN id ELSE (MAX(id) + 1)
FROM @USERS
WHERE email = 'ccc@gmail.com';
Dale K
  • 25,246
  • 15
  • 42
  • 71
Faye D.
  • 833
  • 1
  • 3
  • 16
  • So several consecutive queries for different non-existing emails must return the same id for each? – GSerg Mar 29 '22 at 20:08
  • 2
    Using max could cause issues if you run this concurrently... – Dale K Mar 29 '22 at 20:09
  • Have you considered using the MERGE statement to either update or insert? A much safer approach then what you are suggesting. After that you can select the ID that was updated/inserted if needed – Dijkgraaf Mar 29 '22 at 20:16
  • @Dijkgraaf Please don't recommend `MERGE` unless you also mention [all of the caveats](https://sqlblog.org/merge). – Aaron Bertrand Mar 29 '22 at 20:27
  • 2
    Faye what are you then going to _do with_ `MAX(id)+1`? Are you going to wait and insert it later? This is a major concurrency bug waiting to happen. Please see [Please stop using this UPSERT anti-pattern](https://sqlperformance.com/2020/09/locking/upsert-anti-pattern). – Aaron Bertrand Mar 29 '22 at 20:37
  • @DaleK I've been told this before - in another topic (https://stackoverflow.com/questions/71632815/get-single-row-from-mssql-query-without-while-loop) - but unfortunately field `id` is simply declared as int, no primary key, not any other technique to ensure a unique key... So I don't know how to make sure no issues arise from this... – Faye D. Mar 29 '22 at 20:37
  • @FayeD. worst case you lock the table for your exclusive use until you have made use of the new id. You should also show us how you are using the new id in order for us to advise. – Dale K Mar 29 '22 at 20:38
  • 2
    @FayeD. If you can't change the table to make a primary key/indentity, instead add a [`SEQUENCE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql). This is a completely separate object in the database, so you don't have to make any changes to the table. Then use the `NEXT VALUE FOR` the sequence to get your new ID values. Sequence values are never re-used, so if you want to get the new ID early and insert it later, things are still safe. – Joel Coehoorn Mar 29 '22 at 20:40
  • @JoelCoehoorn yup, unfortunately I don't have any control over that table's design. – Faye D. Mar 29 '22 at 20:40
  • As I commented in that other topic I mentioned in my previous comment, "This will be used within a WooCommerce webhook that will be syncing an e-shop's new orders to the merchant desktop app that's responsible for issuing the vouchers, keeping track of the inventory, etc... I can't imagine two people ordering at the exact same moment, plus WooCommerce (I think) puts webhook tasks in a queue." So, in practice I doubt there will be any issue, but ideally, I'd like to do something to ensure that myself too... – Faye D. Mar 29 '22 at 20:42
  • 3
    Two people never order things at the exact same moment, until they do. Why take the chance when you can actually prevent it? – Aaron Bertrand Mar 29 '22 at 20:43
  • ^^^ what he said... assuming something will never happen is asking for trouble. – Dale K Mar 29 '22 at 20:44
  • So what's the best measure to take for this? A lock before the insert into and an unlock after it? – Faye D. Mar 29 '22 at 20:53
  • 1
    It's still a SEQUENCE. Again: no changes to the table required to create this. Anything else you do **WILL** eventually create an ID conflict. That's a promise. – Joel Coehoorn Mar 29 '22 at 20:55
  • @JoelCoehoorn though it's way off-topic, can you help me figure this `SEQUENCE` usage in my case? Unfortunately SQL Server isn't my cup of tea, I barely know the basics of it. – Faye D. Mar 29 '22 at 21:00
  • 1
    @FayeD. 1) Read the docs 2) ask a new question :) – Dale K Mar 29 '22 at 21:02
  • @DaleK here it is sir! https://stackoverflow.com/questions/71669547/lock-table-for-retrieving-max-id-until-the-max-id-1-is-inserted-into-the-tab if you wish to help me on that one too! :) – Faye D. Mar 29 '22 at 22:41

2 Answers2

1

The following should satisfy syntax requirements:

select coalesce(max(id), (select coalesce(max(id), 0) + 1 from @users))
from @users
where email = 'ccc1@gmail.com'

That being said, the max id could change between selecting and using it in another query.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • 1
    @dale i used `max(id)` trick. Aggregate functions always return one row. I have revised anyway but here is a fiddle showing that it worked https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=073677d7f346658bd3af97ecbba37dc4 – Salman A Mar 29 '22 at 20:18
  • 1
    I keep forgetting that :) – Dale K Mar 29 '22 at 20:20
  • 1
    I do too. I actually run such queries to be sure. – Salman A Mar 29 '22 at 20:21
1

Two sub-queries inside a coalesce should do the job.

DECLARE @TestEmail VARCHAR(50) = 'aaa@gmail.com';

SELECT 
    COALESCE((SELECT TOP 1 id FROM @USERS WHERE email = @TestEmail), (SELECT MAX(id) + 1 FROM @USERS), 1);

This will only work in a simple scenario when you don't call this concurrently, and you make use of the returned ID before you would ever call this again. Otherwise you would need to perform some locking to ensure it works as expected.

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • I'm only marking this as the correct answer as it was posted first. Because other than that, the other answer is the same! – Faye D. Mar 29 '22 at 20:44
  • What I see is the opposite, yours posted 37 mins ago, and the other one 39 mins ago, but if you say so, I trust you! Thanks for being fair! – Faye D. Mar 29 '22 at 20:54
  • OMG, and I'm stupid! :P – Faye D. Mar 29 '22 at 21:01
  • 1
    I ended up using yours as it was clearer and simpler with the only one COALESCE. – Faye D. Mar 29 '22 at 21:19
  • I hadn't forseen this, and didn't mention it in my original question either, but when the customer's email is the same, the new row will still have to be inserted but with the same id of the other row with that email. So, in case of a third row, SQL complained that multiple rows were returned (both/all with the same id, but still it returned an error), therefore I had to use DISTINCT for the first sub-query! Very sneaky one! – Faye D. Mar 29 '22 at 22:59
  • That was solved by the other solution. But TOP is better than DISTINCT. – Dale K Mar 29 '22 at 23:02