I made one table which contains name, id, subject now if I want to update 2 different ID for same subject how do I do this?

Question

0.00/5 (No votes)

See more:

Table Student

Name Id Subject
Komal 1 Maths
Kiran 2 Science
Rohan 3 Maths
Roshan 4 Maths

now I want to update id's of Komal ,Rohan and Roshan whom Subjects are Maths as 5, 6, 7 respectively ..
Which query I use for it?

What I have tried:

Update Student Set Id=5 And Id=6 And Id= 7
where subject='Maths'

But its not working..

Posted 17-May-16 3:03am

Member 12305778

Updated 17-May-16 3:21am

Add a Solution

2 solutions

Solution 2

Don't even try.
You can't do it the way you want, because UPDATE works on a row at a time, so you can't say "first use this value, then that for the next row" - and if you think about it, it's obvious why. What happens if you had four rows using the same subject? What should SQL do with that?
You could possibly do it - unless you have created the column as an IDENTITY field - but, it's fraught with risks.

The idea of an ID field is that it provides a way for everything to know precisely which row you are talking about - it should be a unique value which remains with teh row for it's entire life. So if you have to add a second student with the name "Kiran":

Kiran   5   Geography

You can refer to each Kiran separately by using the ID value, since they will not be the same.
Changing the ID is a bad idea, partly because there will be an intermediate stage where identical ID exist, and partly because in a well designed system you will other tables which depend on the ID value to "link" the data together. For example, you could easily have a second table holding addresses which uses the ID as a "foreign key" to tie the address details to the student. For example, I would use a second table which held "known subjects" and use an ID field to link that to the students instead of duplicating the subject names.

Why do you think that renumbering these would be a good idea? It's almost certainly not!

Posted 17-May-16 3:21am

OriginalGriff

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)

CHill60 · Accepted Answer · 2016-05-17T03:17:00

Quite an unusual requirement - most people would not update an ID.

However, you can do this with CASE e.g.

SQL

update Student set Id = CASE
		WHEN [Name]='Komal' THEN 5 
		WHEN [Name]='Rohan' THEN 6  
		WHEN [Name] = 'Roshan' THEN 7 
		ELSE Id END

Note the fact that they all do maths is irrelevant. There doesn't seem to be any algorithm that can be applied to this strange requirement.

Note also that the default value for the CASE statement is Id otherwise the Id for Kiran will be set to NULL. Otherwise you could do this

SQL

update Student set Id = CASE
		WHEN [Name]='Komal' THEN 5 
		WHEN [Name]='Rohan' THEN 6  
		WHEN [Name] = 'Roshan' THEN 7 
		END
WHERE [Subject] = 'Maths'

What you absolutely should not be doing is finding the MAX(Id) and using a cursor to update these records.