Post reply

Update with case

  • September 26, 2005 at 3:43 am

    #91327

    Hi there,

    I need some help here, nothing happens when I execute the following,

    UPDATE A

       SET A.col_1 = CASE WHEN NOT EXISTS

    (SELECT * 

     FROM         A INNER JOIN

                          B_view ON A.ID_1 = B_view.ID_1 AND

                   A.ID_2 = B_view.ID_2)

     THEN 'N' ELSE 'Y' END;

    When I run it, it comes up with a (1234564 row(s) affected), and when I look in table A nothing has happend?

    I must be missing something here, and I don't know what it is.

    Regards joejoe

  • September 26, 2005 at 6:55 am

    #592955

    Well actually everything gets updated to 'N'.

    You don't need the exists clause here, just use a left join to make this update >>

    UPDATE A Set A.col_1 = CASE WHEN B.col_1 IS NULL THEN 'N' ELSE 'Y' END FROM dbo.A A LEFT OUTER JOIN dbo.ViewA B ON A.id = B.id

  • September 27, 2005 at 3:17 am

    #593200

    Thanks RGR'us

    But when I run your script, the same thing happens

    (1234564 row(s) affected)

    But nothing is changed, it's all NULLs ???

     

     

     

     

  • September 27, 2005 at 5:19 am

    #593225

    The probelm is solved, allways remember to see if there is triggers on table

    Regards Joejoe 

  • September 27, 2005 at 6:50 am

    #593246

    Yup .

Viewing 5 posts - 1 through 4 (of 4 total)

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