How can I append on a row number on to a row insertion if a match already exists?

I have a table of users with an email such as:

johnsmith@gmail.com

johnsmith@gmail.com

I am attempting to batch update users' referral codes with a readable entry.

I will be setting this code to the first part of their email (prior to the @ symbol) up to a limit of 12 characters.

@

If there are more than ONE of these matches, such as :

johnsmith@gmail.com and johnsmith@aol.com, then the second will have a number increment appended on to the end.

johnsmith@gmail.com

johnsmith@aol.com

This should result in the referral codes being:

johnsmith and johnsmith1 etc.

johnsmith

johnsmith1

Right now, even if there are just two, I am getting:

johnsmith1 and johnsmith2.

johnsmith1

johnsmith2

Ideally, if there is only one entry, there should be no number appended.

How can I do this?

This is what I currently have:

UPDATE auth.user_referral_codes
SET referral_code = CONCAT((
SELECT LEFT(LEFT(email, STRPOS(email, '@') - 1), 12)
FROM auth.users
WHERE id = auth.user_referral_codes.user_id
) , (
SELECT row_number FROM (
SELECT row_number()
OVER (
PARTITION BY (SELECT LEFT(LEFT(email, STRPOS(email, '@') - 1), 12))
)
FROM auth.users
WHERE id = auth.user_referral_codes.user_id
) as row_number_subquery
));


2 Answers
2

Your code seems so complicated:

update auth.user_referral_codes urc
set referral_code = (left(left(email, strpos(email, '@') - 1), 12) ||
(case when seqnum > 1 then seqnum::text else '' end)
)
from (select u.*,
row_number() over (partition by left(left(email, strpo(email, '@') - 1), 12) order by user_id) as seqnum
from users u
) u
where urc.user_id = u.id;


Postgres supports a FROM clause in UPDATEs. It is quite useful.

FROM

UPDATE

error: missing FROM-clause entry for table "u"

With a little tweaking of Gordon Linoff's answer, this works:

UPDATE auth.user_referral_codes urc
SET referral_code = (
LEFT(LEFT(email, strpos(email, '@') - 1), 12)
|| (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
)
FROM (
SELECT
auth.users.*,
row_number() OVER (
PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
ORDER BY id
) AS seqnum
FROM auth.users
) AS u
WHERE urc.user_id = u.id;


Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.