Member Avatar for ayyazm64

Hi,
I have a problem that in a database (named DDS) i can change or delete records in all tables but there is one which i can't. There isn't any primary or foreigh keys(There is one dependency, a view). So could you please tell me that is there any way that while designing a database in SQL Server we can lock a table and is there any way to remove that lock and change the values of a field. There are two constraints on this table as:

USE [DSS]
GO
ALTER TABLE [dbo].[tblGlob] DROP CONSTRAINT [DF_tblGlob_BYDEFT]

USE [DSS]
GO
ALTER TABLE [dbo].[tblGlob] DROP CONSTRAINT [DF_tblGlob_SetValue]

(these two fields BYDEFT & SETVALUE have data type as bit.)

Even if i delete these constaints, still i can't change the data in this table. I am trying to change a field named company_address. I can't even delete a record from this table.

My login has all permissions.

Here is the error i get:- " The row values updated/deleted either donot make row unique or they alter multiple rows(2 rows)"
Regards!
Ayyaz

  • 3 Contributors
  • 6 Replies
  • 240 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by ChrisHunter

Dani AI

Generated

Most likely cause: the row you’re trying to edit or delete isn’t uniquely identifiable, so the client (SSMS’s grid or whatever UI you’re using) cannot build a WHERE clause that targets a single record. That situation commonly happens when a table has no primary key or unique index and there are duplicate rows. It can also be caused by an INSTEAD OF/AFTER trigger (on the table or an updatable view that references it) that intercepts and blocks changes.

Quick checklist to diagnose

  • Try an explicit T-SQL update/delete that targets a row uniquely (not the grid). If that works, the UI was the problem.
  • Search for triggers on the table.
  • Look for duplicate rows so you know what would prevent a unique match.
  • If a view or trigger is involved (you mentioned a view dependency), check whether the view is updatable or has INSTEAD OF triggers.

Useful test and fix examples

Find duplicates (replace the partition list with the columns that should uniquely identify a row):

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY /* col1, col2, ... */ ORDER BY (SELECT 0)) AS rn
  FROM dbo.tblGlob
)
SELECT * FROM cte WHERE rn > 1;

Quick single-row update (test only — TOP without ORDER BY is nondeterministic):

UPDATE TOP (1) dbo.tblGlob
SET CompanyAddress = 'New address'
WHERE /* a predicate such as CompanyName = 'X' */;

Permanent fix: add a unique identifier so rows are always addressable. Example (adds a surrogate key):

ALTER TABLE dbo.tblGlob
ADD RowID INT IDENTITY(1,1) NOT NULL;

ALTER TABLE dbo.tblGlob
ADD CONSTRAINT PK_tblGlob_RowID PRIMARY KEY CLUSTERED (RowID);

If you cannot change schema, create a unique index on true key columns (after cleaning duplicates), or continue using explicit T-SQL deletes/updates that uniquely identify rows. Also check for triggers (run sys.triggers for the table) and for any INSTEAD OF triggers on related views.

As described the symptom, and as and requested, posting the exact UPDATE/DELETE you used and any trigger/view definitions will let helpers give a targeted command if you want further help.

Recommended Answers

All 6 Replies

Member Avatar for IIM

Provide complete table structure then only I can help u

Member Avatar for ChrisHunter

Can you post the SQL statements you were using please?

Member Avatar for ayyazm64

Here is the DDL for this table:

USE [DSS]
GO
/****** Object:  Table [dbo].[tblGlob]    Script Date: 02/21/2013 14:09:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGlob](
    [PrinterName] [varchar](50) NULL,
    [GlobalExpiry] [varchar](50) NULL,
    [DBSTATUS] [bit] NULL,
    [BYDEFT] [bit] NULL,
    [CompanyName] [varchar](200) NULL,
    [CompanyAddress] [varchar](200) NULL,
    [SettingName] [varchar](500) NULL,
    [SetValue] [bit] NULL,
    [Organization] [varchar](200) NULL,
    [Phone] [varchar](50) NULL,
    [MODIFIER] [varchar](50) NULL,
    [LAST_MODIFIED] [datetime] NULL,
    [OPERATION_TYPE] [varchar](50) NULL,
    [BankName] [varchar](500) NULL,
    [Logo] [image] NULL,
    [Email] [varchar](200) NULL,
    [Website] [varchar](200) NULL,
    [EmailServer] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Ayyaz

Edited by ayyazm64
Member Avatar for ChrisHunter

and the transaction statements like the delete, update and insert?

Member Avatar for ayyazm64

I am trying to change the data in one of the fields named company_Address manually right sitting on the table.

Member Avatar for ChrisHunter

You need to be less vague about what you want to do. Do you want to insert net data, update existing data, delete data? What have you tried so far to solve the problem? and by that I mean what have you done other than create the table.

I will try to help you but I need to know what it is you actually want and see what you have already tried before I can

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.