使用更新的SQL查询，并为每个 [英] sql query using update and for each

问题描述

我有一个表，其中包含名称，地址，phn否，空字段.每条记录都有empid = emp0001.
任何机构都可以帮助我找到如何使用sql查询针对每条记录用唯一的empid更新此表.
例如:
我的表格是:EMP_INFO

Hi,

I have a table with fields name,address,phn no, empid. there empid=emp0001 for every record.
Can any body help me to find how to update this table with unique empid against every record using sql query.
eg:
my table is: EMP_INFO

name   address      phn_no           empid

neha   delhi      7894561231        EMP0001
ritu   bangalore  1234567895        EMP0001
SAUMYA LUCKNOW    7894561235        EMP0001
...... .......    ..........        .......

这样，我就有了1000条记录.我想通过对每条记录设置唯一的Empid来更新表

我想要的表是:EMP_INFO

This way i have got 1000 records. I want to update the table by seting unique empid against every record

table i want is : EMP_INFO

name   address      phn_no           empid
neha   delhi      7894561231        EMP0001
ritu   bangalore  1234567895        EMP0002
SAUMYA LUCKNOW    7894561235        EMP0003
...... .......    ..........        .......

预先感谢您的帮助.

[edit]已添加代码块-OriginalGriff [/edit]

Thanks in advance for help.

[edit]Code blocks added - OriginalGriff[/edit]

推荐答案

您需要运行两个SQL语句:

You need to run two SQL statements:

SqlCommand com1 = new SqlCommand("SELECT * FROM EMP_INFO", con);
SqlDataReader r = com1.ExecuteReader();
int i = 1;
while (r.Read())
   {
   string name = (string) r["name"];
   string address = (string) r["address"];
   string phone = (string) r["phn_no"];
   SqlCommand com2 = new SqlCommand(string.Format("UPDATE EMP_INFO SET empid=EMP{0} WHERE name=@N AND address=@A AND phn_np=@P", i.ToString("D4")), con);
   com2.Parameters.AddwithValue("@N",name);
   com2.Parameters.AddwithValue("@A",address);
   com2.Parameters.AddwithValue("@P",phone);
   com2.ExecuteNonQuery();
   i++;
   }

您可以按照以下步骤实现所需的内容.

-使用以下
创建一个过程
*声明一个从0001开始的变量.
例如:
DECLARE @idNumber INT
SET @idNumber = ''0001''

*使用光标获取所有具有empemp emp0001的行.

*读取游标中的每个元素，并使用连接值 "emp"+@idNumber 更新它们，然后idNumber可以每次增加1.
SET @idNumber = @idNumber + 1 (在where子句中使用empId附带的任何其他文件)

BR//
Harsha

You can follow the Below Steps to achieve what you are looking for.

-- Create a Procedure with the following

* Declare A variable that starts from 0001.
eg :
DECLARE @idNumber INT
SET @idNumber = ''0001''

* Fetch all the rows that has empid emp0001 using cursor.

* Read each Element in the Cursor and Update them with concatenated value of "emp"+@idNumber and the idNumber then can be increased by 1 every time.
SET @idNumber = @idNumber + 1 ( use any other filed along with the empId in where clause )

BR//
Harsha

作为OP标记的SqlServer-尝试通过以下方式找出

As OP Tagged SqlServer-- try to find out in the following way

1   AA  123456  2011-05-11 10:22:27.667 1
2   BB  1234    2011-05-11 10:22:39.210 1
3   CC  51234   2011-05-11 10:22:51.770 1
4   DD  123456  2011-05-11 10:23:03.593 1
5   AA  123456782011-05-11 10:23:39.973 1
6   AA  123456  2011-05-11 10:25:59.437 1

// As

declare @Count int
declare @Id  varchar(60)
declare @loop int
set @loop = 1
set @id=''Emp''

select @Count= Count(*) from Test7
while(@loop<=@Count)
begin
Update Test7 set sno = @Id+ cast(Id as varchar) where sno= ''1''
set @loop= @loop+1
end

select * from Test7

/* after executing above query result is */

1   AA  123456  2011-05-11 10:22:27.667 Emp1
2   BB  1234    2011-05-11 10:22:39.210 Emp2
3   CC  51234   2011-05-11 10:22:51.770 Emp3
4   DD  123456  2011-05-11 10:23:03.593 Emp4
5   AA  12345678 2011-05-11 10:23:39.973 Emp5
6   AA  123456  2011-05-11 10:25:59.437 Emp6

这篇关于使用更新的SQL查询，并为每个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！