使用更新的SQL查询，并为每个

问题描述

我有一个表，其中包含名称，地址，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]

推荐答案

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++;
   }

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查询，并为每个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！