我创建了一个包含name，id，subject的表，如果我想为同一主题更新2个不同的ID我该怎么做？ [英] 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?

问题描述

表学生



姓名身份对象

Komal 1数学

Kiran 2科学

Rohan 3数学

Roshan 4数学



现在我想更新Komal，Rohan和Roshan的id，主题是数学分别为5,6,7 ..

我用它来查询哪个？



我尝试了什么：



更新学生集ID = 5而Id = 6且Id = 7

其中subject ='数学'



但它无法正常工作..

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

推荐答案

相当不寻常的要求 - 大多数人都不会更新ID。



但是，您可以使用CASE执行此操作，例如

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

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

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

注意他们都做数学的事实是无关紧要的。似乎没有任何算法可以应用于这个奇怪的要求。



另请注意，CASE语句的默认值为 Id 否则Kiran的Id将设置为NULL。否则你可以这样做

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

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

你的腹肌olutely 不正在做的是找到MAX（Id）并使用光标更新这些记录。

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

甚至不要尝试。

你不能按照你想要的方式去做，因为UPDATE一次只能处理一行，所以你不能说首先使用这个值，然后再用下一行 - 如果你考虑它，这是显而易见的原因。如果您使用相同主题的四行会发生什么？ SQL应该怎么做？

你可能会这样做 - 除非你已经将列创建为IDENTITY字段 - 但是，它充满了风险。



ID字段的想法是，它为所有事物提供了一种方法，可以准确地知道您正在谈论的是哪一行 - 它应该是一个独特的值，它在整个生命周期中仍然存在。因此，如果您必须添加名为Kiran的第二名学生：

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

您可以分别参考每个Kiran使用ID值，因为它们不会相同。

更改ID是一个坏主意，部分原因是存在相同ID的中间阶段，部分原因是在设计良好的系统中您将依赖于ID值的其他表将数据链接在一起。例如，您可以轻松地拥有一个第二个表，其中包含使用ID作为外键的地址，以将地址详细信息绑定到学生。例如，我会使用第二个表，其中包含已知主题并使用ID字段将其链接到学生而不是复制主题名称。



为什么你认为重新编号这些是个好主意吗？这几乎肯定不是！

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!

这篇关于我创建了一个包含name，id，subject的表，如果我想为同一主题更新2个不同的ID我该怎么做？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！