Post reply

Probably a very simple question.

  • August 24, 2005 at 10:54 am

    #363483

    I am no SQL guru so this question may be very stupid. I have a table called ModuleSettings that has the following columns:

    ModuleID int

    SettingName nvarchar

    SettingValue nvarchar

    Sample data would look like this:

    ModuleID | SettingName | SettingValue

    -------------------------------------

    100 | GroupType | Keyword

    100 | ShowPaging | False

    250 | GroupType | Keyword

    250 | ShowPaging | False

    300 | GroupType | Best Selling

    300 | ShowPaging | False

    430 | GroupType | Highest Inventory

    430 | ShowPaging | False

    500 | GroupType | Keyword

    500 | ShowPaging | False

    All I want to do is update SettingValue with a value of 'True' where SettingName = 'ShowPaging' but ONLY if the ModuleID for that record has a corresponding record where SettingName = 'GroupType' and SettingValue = 'Keyword'

    Are there any gurus looking down on me snickering right now because this is super easy? If so, please respond because I don't know how to approach this.

    Thank you,

    pkoanui

  • August 24, 2005 at 11:17 am

    #584704

    Hey, it's not as easy as you might think.  Try this (untested):

    update m1

    set SettingValue = 'True'

    from ModuleSettings m1

    where exists (select m2.ModuleID from ModuleSettings m2 where m2.SettingName = 'GroupType' and m2.SettingValue = 'Keyword' and m2.ModuleID = m1.ModuleID) and m1.SettingName = 'ShowPaging'

    Regards

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 24, 2005 at 11:41 am

    #584717

    Thank you very much. That worked like a charm. I really appreciate it.

    Paul

  • August 25, 2005 at 12:48 pm

    #585133

    Another alternative would be to do a self join:

     

    update modulesettings

    set SettingValue = 'TRUE'

    from modulesettings  inner join modulesettings ms2

    on modulesettings.ModuleID = ms2.ModuleID

    where modulesettings.SettingName = 'ShowPaging' and

          ms2.SettingName = 'GroupType' and

          ms2.SettingValue = 'Keyword'

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

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