Post reply

A simple One - but the answer escapes me

  • 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?

  • June 9, 2005 at 1:57 pm

    #564382

    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 .

  • June 9, 2005 at 2:01 pm

    #564386

    Thanks Remi

  • June 9, 2005 at 2:47 pm

    #564395

    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]

  • June 9, 2005 at 2:52 pm

    #564398

    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

  • June 9, 2005 at 3:03 pm

    #564402

    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]

  • June 9, 2005 at 3:06 pm

    #564404

    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.

  • June 10, 2005 at 12:02 am

    #564477

    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]

  • June 10, 2005 at 1:46 am

    #564497

    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.

  • June 10, 2005 at 5:09 am

    #564537

    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

  • June 10, 2005 at 6:31 am

    #564548

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

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

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