Why cannot I delete (edit) a row with xml value in SSMS?
In SSMS 2008 R2 I execute:
create table aaa(col1 xml);
go
insert into aaa (col1)
values('<ccc>ddd</ccc>')
go 2
Then, open table in SSMS (right-clicking the table in Object Explorer) with "Edit top 200 rows" option, select a row, press delete (keyboard button),
click "Yes" to confirm and receive error [ 1 ]Why cannot I delete or edit a row?
[ 1 ]
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No 开发者_JS百科rows were deleted.
A problem occurred attempting to delete row 1.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).
---------------------------
OK Help
---------------------------
Update:
I thought SSMS is XML-antogonistic, but it is the same with any typescreate table bbb(col1 int);
go
insert into bbb (col1)
values(33)
go 2
This is a limitation of the designer/wizard
Since the rows are identical there is nothing that tells sql server which of those 2 rows to delete, remember it is a program not a human... (this is also a reason to have a PK on a table)
You can however do it from a query window
delete top (1)
from aaa
where convert(varchar(max),col1) = '<ccc>ddd</ccc>'
or with older syntax
set rowcount 1
delete
from aaa
where convert(varchar(max),col1) = '<ccc>ddd</ccc>'
set rowcount 0
精彩评论