August 24, 2005 at 10:54 am
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
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
August 24, 2005 at 11:41 am
Thank you very much. That worked like a charm. I really appreciate it.
Paul
August 25, 2005 at 12:48 pm
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