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

user_6108226 0
Viewed 56

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

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.

This should result in the referral codes being:

johnsmith and johnsmith1 etc.

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

johnsmith1 and 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
    ));
window-functions postgresql sql
2 Answers

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.

user_11440350

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;
user_61082260
Related