A simple One - but the answer escapes me

Question

Post reply

A simple One - but the answer escapes me

gleazenby

SSC Eights!

Points: 800
More actions
June 9, 2005 at 1:56 pm

#89392

How do I run a query where I want to UPDATE a text field with a value containing a possessive.
--------------------------------------
Example 1 (that fails)
UPDATE    on_line_tutorial_menu_level2
SET              menu_lvl2_item = 'Bob's Hobbies'
WHERE     (menu_lvl2_id = 2)
--------------------------------------
Example 2 (that fails)
UPDATE    on_line_tutorial_menu_level2
SET              menu_lvl2_item = "Bob's Hobbies"
WHERE     (menu_lvl2_id = 2)
--------------------------------------
What is the answer to this?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Text field or varchar() field???

UPDATE on_line_tutorial_menu_level2

SET menu_lvl2_item = 'Bob''s Hobbies'

WHERE (menu_lvl2_id = 2)

Also you wouldn't get that error if you were using a stored proc .

gleazenby SSC Eights! Points: 800 More actions · Answer 2

gleazenby

SSC Eights!

Points: 800

June 9, 2005 at 2:01 pm

#564386

Thanks Remi

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Also you wouldn't get that error if you were using a stored proc

Can you explain this?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Now you're forcing me to post......

GO

Create proc dbo.MyProc @menu_lvl2_id as int, @menu_lvl2_item as varchar(50)

AS

SET NOCOUNT ON

UPDATE on_line_tutorial_menu_level2

SET menu_lvl2_item = @menu_lvl2_item

WHERE menu_lvl2_id = @menu_lvl2_id

SET NOCOUNT OFF

GO

EXEC dbo.MyProc 2, 'test'

Select * from dbo.menu_lvl2_item where menu_lvl2_id = 2

EXEC dbo.MyProc 2, 'Bob''s Hobbies'

Select * from dbo.menu_lvl2_item where menu_lvl2_id = 2

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

You've changed the basis a bit. It doesn't meet the original request anymore.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

menu_lvl2_id seems to be the primary key

menu_lvl2_item seems to be some book title or something like that.

I wouldn't expect him to want to update the whole table with the same value.. whould him??

But if you think I missed something, I'm all hears.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Yes, you're missing the fact that the original example will always fail. No matter if in a batch or a stored procedure. That's what I meant.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

peter meachem SSC Rookie Points: 33 More actions · Answer 8

You need to replace a single quote with a double one. How you do this depends on the language you are using, but Replace works fine in VB.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 9

Try:

SET QUOTED_IDENTIFIER OFF

DECLARE @Name varchar (20)

SET @Name = "Bob's bad name"

SELECT "Bob's bad name", @Name

SET QUOTED_IDENTIFIER ON

Andy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Not the way I've written it... assuming I got the PK right and that I understand what he wants to do .