Probably a very simple question.

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

    Paul

  • 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