使用不同的值更新多个列

               I have table following this format :

Id   BillNo:      Voucher No:
1   W2015-16/0001  W2015-16/0001
2   W2015-16/0002  W2015-16/0002
3   W2015-16/0003  W2015-16/0003
4   W2015-16/0004  W2015-16/0004
5   W2015-16/0005  W2015-16/0005
6   W2015-16/0006  W2015-16/0006
7   W2015-16/0007  W2015-16/0007
8   W2015-16/0008  W2015-16/0008
9   W2015-16/0009 W2015-16/0009
10   W2015-16/0010  W2015-16/0010

But Instead of this Format: Now I want to Update All "Voucher No" and "Bill No" below on this format :

但不是这种格式:现在我想在此格式下面更新所有“凭证否”和“比尔否”:

Id   BillNo:      Voucher No:
1   W0001/2015-16 W0001/2015-16
2   W0002/2015-16 W0002/2015-16
3   W0003/2015-16 W0003/2015-16
4   W0004/2015-16 W0004/2015-16
5   W0005/2015-16 W0005/2015-16
6   W0006/2015-16 W0006/2015-16
7   W0007/2015-16 W0007/2015-16
8   W0008/2015-16 W0008/2015-16
9   W0009/2015-16 W0009/2015-16
10  W0010/2015-16 W0010/2015-16
11  W0011/2015-16 W0011/2015-16

like this i have a 1000+ records i should update , But i don't know is this possible to do in Lesser time, Kindly give your suggestion , i am new to SQL Thanks Advance

像这样我有1000多条记录我应该更新,但我不知道这可能在较短的时间内做,请提出你的建议,我是新来的SQL感谢提前

3 个解决方案

#1

UPDATE YourTable
SET BillNo = SUBSTRING(BillNo,1,1) + SUBSTRING(BillNo,10,4) + '/' + SUBSTRING(BillNo,2,7)
, VoucherNo = SUBSTRING(VoucherNo,1,1) + SUBSTRING(VoucherNo,10,4) + '/' + SUBSTRING(VoucherNo,2,7)

Test:

DECLARE @a NVARCHAR(max) = 'W2015-16/0001' 
SELECT SUBSTRING(@a,1,1) +
SUBSTRING(@a,10,4) + '/' + SUBSTRING(@a,2,7)

#2

Assuming everything is in a fixed format, then you would just use update and string manipulations:

假设所有内容都是固定格式,那么您只需使用更新和字符串操作:

update table t
    set billno = left(billno, 1) + right(billno, 4) + '/' + substring(billno, 2, 7),
        VoucherNo = left(VoucherNo, 1) + right(VoucherNo, 4) + '/' + substring(VoucherNo, 2, 7);

#3

There is no straight forward way to achieve this. You may need to define some patterns in SQL to replace and play with all string functions. Here is

没有直接的方法来实现这一目标。您可能需要在SQL中定义一些模式以替换和播放所有字符串函数。这是

declare @name varchar(50) ='W2015-16/0001';
select SUBSTRING(STUFF(@name,2,0,(SUBSTRING(@name,CHARINDEX('/',@name) + 1,len(@name)) + '/')),0,14)
--W0001/2015-16

This gives you the result that you are looking for. So you need to set this into your update statement by joining with the table by ID. This would work only if the pattern you have given is correct.

这可以为您提供所需的结果。因此,您需要通过ID连接表来将其设置为更新语句。只有在您给出的模式正确时,这才有效。

#1

UPDATE YourTable
SET BillNo = SUBSTRING(BillNo,1,1) + SUBSTRING(BillNo,10,4) + '/' + SUBSTRING(BillNo,2,7)
, VoucherNo = SUBSTRING(VoucherNo,1,1) + SUBSTRING(VoucherNo,10,4) + '/' + SUBSTRING(VoucherNo,2,7)