我有一个关于sql删除的问题 [英] I have a question on sql delete

问题描述

我有一个像userid，username，activestatus这样的列名列表。但我想从这个表中删除表中间的1行，然后下一行将被替换为同一个已删除的记录用户ID。



希望这个清楚...



请帮忙....



问候

Ram

解决方案

一般来说，你不能 - 这意味着UserID必须通过系统渗透，并且可能必须反映出来在其他表中 - 他们会使用外键将该表中的信息与特定用户相关联，只需移动主表中的UserID就会导致错误的数据关联。



还有一个问题是SQL实际上没有下一个记录的概念 - 它可以为特定排序顺序的记录分配行号，但是没有内在的下一个或以前的记录（如果有的话，也没办法访问它们）



和事情更糟糕的是，如果两个用户快速连续删除一行会发生什么？需要非常仔细的管理以确保在每种情况下删除正确的行。



相反，使用与行中的RowNumber相关的虚拟用户ID特定的排序顺序。或者更好 - 与差距生活在一起。这对每个人来说都是一个不那么令人困惑的情况！

如果我找到你，你打算做的操作是更新 [ ^ ]那就是你要在具有给定 userid的行中更改 useraname 和 activestatus 值。

例如

  UPDATE  mytable  SET  username = '  NewUsername'，activestatus = '  Y'  WHERE  userid = '  foo'  

I have a one table like userid,username, activestatus these are column names. But i want to delete from this table for 1 row that is middle of the table, then the next row will be replaced in the same deleted record userid.

hope this clear...

please help on this....

Regards
Ram

解决方案

Generally speaking, you can't - it would mean that UserIDs would have to percolate down through the system and potentially that would have to be reflected in other tables - they would use Foreign keys to relate the info in that table to a specific user and just "moving" the UserID in you main table would cause the wrong data to be associated.

There is also the problem that SQL doesn't really have a concept of "next" record as such - it can assign row numbers to records for a particular sort order, but there is no intrinsic "next" or "previous" record (and no way to access them if there was)

And to make matters worse, what happens if two users delete a row in quick succession? It will take very careful management to ensure that the right row is deleted in each case.

Instead, use a "dummy" userID which is related to the RowNumber in a particular sort order. Or better still - live with gaps. It's a much less confusing situation for everybody!

If I got you, the operation you intend to do is an UPDATE [^] that is you want to change the useraname and activestatus values in a row having the given userid.
e.g.

UPDATE mytable SET username='NewUsername', activestatus='Y' WHERE userid='foo'

这篇关于我有一个关于sql删除的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！