英文:
Update a column with a varchar + incrementing int
问题
我试图更新表中的两列，通过从同一表中提供一个列表数据。以下是我想要使用的格式更新这两列的方式：
LOGIN EMAIL
----- -----
5717-9889-0001@xxxx.com 5717-9889-0001@xxxx.com
5717-9889-0002@xxxx.com 5717-9889-0002@xxxx.com
5717-9889-0003@xxxx.com 5717-9889-0003@xxxx.com
以下是我写了一半的查询。我实际上不确定这是否是正确的方式，因为我是从另一个帖子中参考的。显然这不正确。
DECLARE @IncrementValue1 INT
DECLARE @IncrementValue2 INT
SET @IncrementValue1 = 0001
SET @IncrementValue2 = 0001
UPDATE
USERTEST
SET
LOGIN = '5717-9889-' + @IncrementValue1 + '@xxxx.com',
EMAIL = '5717-9889-' + @IncrementValue2 + '@xxxx.com'
WHERE
USERID IN (SELECT
USERID
FROM
USERTEST
WHERE
LOGIN IN ('advw@12233318007262',
'caeqveewe',
'zxy@vdv',
'zstclair',
'zpasigna',
'zoe_tadvadv',
'zmadvadvadv',
'zadvadvadv',
'zielinsm@advadvadv.com',
'zhouxinhy',
'ZHEJIANG@BBAGGS',
'ZHE@avadvadv'))
希望能够提供帮助。期望的结果应该如下所示。每个文档大约有200个ID。
LOGIN EMAIL
----- -----
5717-9889-0001@xxxx.com 5717-9889-0001@xxxx.com
5717-9889-0002@xxxx.com 5717-9889-0002@xxxx.com
5717-9889-0003@xxxx.com 5717-9889-0003@xxxx.com
5717-9889-0004@xxxx.com 5717-9889-0004@xxxx.com
5717-9889-0005@xxxx.com 5717-9889-0005@xxxx.com
5717-9889-0006@xxxx.com 5717-9889-0006@xxxx.com
5717-9889-0007@xxxx.com 5717-9889-0007@xxxx.com
英文:
I'm trying to update the two columns in a table by feeding a list data from the same table. Below is the format I want to update the two columns with
LOGIN EMAIL
----- -----
5717-9889-0001@xxxx.com 5717-9889-0001@xxxx.com
5717-9889-0002@xxxx.com 5717-9889-0002@xxxx.com
5717-9889-0003@xxxx.com 5717-9889-0003@xxxx.com
Below is the query I wrote halfway. I'm actually not sure this is the correct way as I have refereed this from another thread. Obviously it not correct.
DECLARE @IncrementValue1 INT
DECLARE @IncrementValue2 INT
SET @IncrementValue1 = 0001
SET @IncrementValue2 = 0001
UPDATE
USERTEST
SET
LOGIN = '5717-9889-' + @IncrementValue1 + '@xxxx.com',
EMAIL = '5717-9889-' + @IncrementValue2 + '@xxxx.com'
WHERE
USERID IN ( SELECT
USERID
FROM
USERTEST
WHERE
LOGIN IN ('advw@12233318007262',
'caeqveewe',
'zxy@vdv',
'zstclair',
'zpasigna',
'zoe_tadvadv',
'zmadvadvadv',
'zadvadvadv',
'zielinsm@advadvadv.com',
'zhouxinhy',
'ZHEJIANG@BBAGGS',
'ZHE@avadvadv'))
Appreciate any help on this
Desired result should look like this. I have around 200+ ids per Document
LOGIN EMAIL
----- -----
5717-9889-0001@xxxx.com 5717-9889-0001@xxxx.com
5717-9889-0002@xxxx.com 5717-9889-0002@xxxx.com
5717-9889-0003@xxxx.com 5717-9889-0003@xxxx.com
5717-9889-0004@xxxx.com 5717-9889-0004@xxxx.com
5717-9889-0005@xxxx.com 5717-9889-0005@xxxx.com
5717-9889-0006@xxxx.com 5717-9889-0006@xxxx.com
5717-9889-0007@xxxx.com 5717-9889-0007@xxxx.com
答案1
得分: 0
使用row_number()可以获取递增的ID，并使用LPAD将这些整数添加前导零：
如果您需要连接两个字符串，请使用||（+是加法运算符）。
如果您想从11开始，例如，将10添加到row_number()：LPAD(10 + row_number() over(), 4, '0' )
UPDATE USERTEST
SET
LOGIN = '5717-9889-' || LPAD(row_number() over(), 4, '0' ) || '@xxxx.com',
EMAIL = '5717-9889-' || LPAD(row_number() over(), 4, '0' ) || '@xxxx.com'
WHERE LOGIN IN ('advw@12233318007262',
'caeqveewe',
'zxy@vdv',
'zstclair',
'zpasigna',
'zoe_tadvadv',
'zmadvadvadv',
'zadvadvadv',
'zielinsm@advadvadv.com',
'zhouxinhy',
'ZHEJIANG@BBAGGS',
'ZHE@avadvadv');
英文:
You can get incrimented ids using row_number(), and adding leading zeros to those integers using LPAD :
If you need to concatenate two strings use || ( + is the addition operator )
If you want to start from 11 for example add 10 to row_number() : LPAD(10 + row_number() over(), 4, '0' )
UPDATE USERTEST
SET
LOGIN = '5717-9889-' || LPAD(row_number() over(), 4, '0' ) || '@xxxx.com',
EMAIL = '5717-9889-' || LPAD(row_number() over(), 4, '0' ) || '@xxxx.com'
WHERE LOGIN IN ('advw@12233318007262',
'caeqveewe',
'zxy@vdv',
'zstclair',
'zpasigna',
'zoe_tadvadv',
'zmadvadvadv',
'zadvadvadv',
'zielinsm@advadvadv.com',
'zhouxinhy',
'ZHEJIANG@BBAGGS',
'ZHE@avadvadv');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论