update the records

[Bhavsing]

Hi Team,

This is Bhavsing,

I'm new learner of the SQL Server, 

I have one small doubt that if while we have a all rows with same value then how do we update a different values.

Could you please help me how to do, Thanks!

[#2 · Accepted solution]

@Bhavsing 

Its better to have an identity column or a column with a Primary key.

Identity Column -> In SQL Server, an identity column is a special type of column that is commonly used as the primary key of a table. An identity column is a column whose values are generated automatically by the database system, typically incrementing by 1 for each new row added to the table. The purpose of an identity column is to provide a unique, sequential number for each row in the table.

Example -

CREATE TABLE my_table (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50),
age INT
);

Update Statement -
UPDATE my_table
SET my_column = 'new_value'
WHERE my_column = 'old_value';

You can manually update the same via GUI as well.

Hope it's clear------- If yes, reply and hit like ----- If not reply with your query in more detail----

[#3]

One of the basic rules of creating tables is that you need to make all rows unique somehow. A primary key constraint is typically used to say that the values in one or more columns is unique over all rows.

So in your case, it likely goes on the empid column and there is a unique constraint you can put on empname if that should be unique as well. The other reply mentioned identity values, and they are good for making one type of key (a surrogate key that is used for programing), but it is typically essential to have some value that you have a uniqueness condition in your tables that a user could change.

If you want to update one of the rows (if deleting all the rows isn't an option), use something like:

UPDATE top (1) MyTable
SET emp_Id = something useful
WHERE emp_id = 1;

[#4]

@Bhavsing use CTE query with update statement

with updatetable

as

(select *,row_number() over (partition by empid order by empid) as helcol from TableName)

update updatetable set (field name like empanme)='Ram' where helpcol=1

[#5]

Hello here is an example:

UPDATE top (1) MyTable
SET emp_Id = '123456'
WHERE emp_id = 1;

Yes you need to have a unique primary keys (A way to identifiy a row uniquely).

Also I'v notice that you have a unique phone...Not ideal primary key for an Employee but you could 

update your columns by specifying 

Update mytable

set emp_id='123456'

where phone='9000000000'

Hope this helps;)

[#6]

Hello here is an example:

UPDATE top (1) MyTable
SET emp_Id = '123456'
WHERE emp_id = 1;



Yes you need to have a unique primary keys (A way to identifiy a row uniquely).

Also I'v notice that you have a unique phone...Not ideal primary key for an Employee but you could

update your columns by specifying


Update mytable

set emp_id='123456'

where phone='9000000000'

Hope this helps;)