update not applying

Question

Post reply

update not applying

bodhilove

SSCarpal Tunnel

Points: 4035
More actions
May 16, 2010 at 11:53 pm

#143153

Hi Folks,
I am trying to do the following update
UPDATE [dbo].[Work]
SET Parent_Id = null
WHERE Reference = 58890
the table definition is attached.
The update does not seem to make the Parent_ID = null.
Profiler does not show the query.
When I backup and restore the database on the same instance and run the query it works.
QBE does the same.
No errors occur - just say one row effected.
Please help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Robbert Hof SSCrazy Points: 2778 More actions · Answer 1

From books online:

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

- By using the NEWID function.

- By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Looks like setting it to NULL is not supported.

HTH

Robbert

nigel. SSChampion Points: 11636 More actions · Answer 2

Given your table definition I can see no reason why you cannot do this.

One question, are there any triggers on your Work table?

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 3

Another question, what do you do to verify that the update has occurred?

Nigel
How to post questions

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 4

Is this due to an implicit transaction that is not being committed ?

Clear Sky SQL
My Blog[/url]

Greg Niyazov SSC Eights! Points: 929 More actions · Answer 5

The query looks good, I think. Run it and see if data is updated.

nigel. SSChampion Points: 11636 More actions · Answer 6

gniyazov (5/18/2010)
The query looks good, I think. Run it and see if data is updated.

I think he has, and it's not working. That's the problem.

Nigel
How to post questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

bodhilove (5/16/2010)
When I backup and restore the database on the same instance and run the query it works.

So, after you backup and restore, it works. Then what? It's working, then it's not working. What are you doing in between?

Robbert Hof (5/17/2010)
From books online:
A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
- By using the NEWID function.
- By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.
Looks like setting it to NULL is not supported.
HTH
Robbert

From SSMS:

CREATE TABLE #Work(

[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Work_Id] DEFAULT (newid()),

[Parent_Id] [uniqueidentifier] NULL,

[Reference] [int] NULL,

[WFInstance] [varchar](200) NULL,

[Origin_Id] [uniqueidentifier] NULL,

CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO #Work ([Parent_Id], [Reference], [WFInstance], [Origin_Id])

SELECT NEWID(), 58890, 'Any old text', NEWID()

-- (1 row(s) affected)

SELECT * FROM #Work -- (1 row(s) affected)

UPDATE #Work SET Parent_Id = null WHERE Reference = 58890 -- (1 row(s) affected)

SELECT * FROM #Work -- (1 row(s) affected) [Parent_Id] is now NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden