sql query using update and for each

Question

4.00/5 (1 vote)

See more:

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
...... .......    ..........        .......

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
...... .......    ..........        .......

Thanks in advance for help.

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

Posted 15-May-11 20:49pm

Saumya J Pandey

Updated 15-May-11 21:05pm

OriginalGriff

v2

Add a Solution

5 solutions

Solution 2

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

Posted 15-May-11 21:16pm

OriginalGriff

Comments

Saumya J Pandey 16-May-11 3:20am

thanks OriginalGriff,May be it works. let me check it.

ZeeroC00l 16-May-11 3:35am

@OriginalGriff :
Why not do the Changing process directly in the database using a Cursor and a procedure. ?

OriginalGriff 16-May-11 3:39am

A couple of reasons:
1) It's more complex to explain to what is clearly a beginner - I don't want to sit here explaining what a SP is and how you create one!
2) Why bother? This is clearly something that only needs to be done once: why create an SP which can be run many times?

ZeeroC00l 16-May-11 3:41am

You do have a point there.
But provided if you know the basic wont it be better if you do this task in db itself directly ?

And What about using auto generated sequences or newid() method to fulfill the need ?

OriginalGriff 16-May-11 3:49am

If you have to do this over a huge database, there are two schools of thought there:
1) Do it in the db, so you don't have to transfer all that data back and forth across teh network.
2) Do it in the PC, so you can page it and not tie the database server up with a long running command.

If it's a small DB, then it doesn't matter which way you go. Since his Employee code is only 10,000 people at best, I suspect it is pretty small! :laugh:

I'm not a big fan of autogenerated sequences, except for log files. I find them a PITA because it can be a problem to find out what the ID assigned to your record is when you create it. I prefer to use Guid Id's instead, since these are created at the PC and handled through to the DB.

ZeeroC00l 16-May-11 4:24am

Thanks for the Clarification My +5. :)

But again a few more questions were raised on GUID id's.
Since these GUID id's are pretty huge in size, and they go against the rule of thumb for clustered index key, and it can fragment and page split the data / index page, In your opinion what makes it more advisable to use?

Solution 4

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

Posted 15-May-11 21:32pm

ZeeroC00l

Updated 15-May-11 21:36pm

v2

Comments

Saumya J Pandey 16-May-11 3:35am

thanks ZeeroC00l

ZeeroC00l 16-May-11 3:42am

Glad i could help

Solution 1

Ehm, do you mean :

SQL

UPDATE tablename SET [name] = 'new value', [address] = 'new addr', [phn no] = 'new phone' WHERE empid='emp0001'

Good Luck!

Eduard

Posted 15-May-11 20:59pm

Eduard Keilholz

Comments

Saumya J Pandey 16-May-11 3:02am

no eduard its not that.
wanna set the empid and not the other fields.

Solution 3

I would recommend making one of your columns a primary key.
If none of them can be made a primary key, then add a new column that is the primary key and then update rows based on this column.

If EmpId is unique, then you can update rows based on this column.

Posted 15-May-11 21:20pm

Abhinav S

v2

Comments

Saumya J Pandey 16-May-11 3:22am

Abhinav S,empid field is not possible to make it primary key as it already contains data

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mahendra.p25 · Accepted Answer · 2011-05-15T21:57:00

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