Solved

How to conditionally update key/value pair table in MS SQL?

Posted on 2009-04-01
7
187 Views
Last Modified: 2012-05-06
I have a table with a key/value pair that I want to update. My table of ID's and their key/value pairs is below.

What I want to do is set "SettingEnable" and "SettingTracking" to "True" ONLY IF one or both of them are already "True". Can anyone help with this SQL statement?

ID      Name      Value
12007      SettingEnable      FALSE
12007      SettingTracking      FALSE
12228      SettingEnable      TRUE
12228      SettingTracking      TRUE
12245      SettingEnable      TRUE
12245      SettingTracking      FALSE
12249      SettingEnable      TRUE
12249      SettingTracking      FALSE
12315      SettingEnable      TRUE
12315      SettingTracking      TRUE
12350      SettingEnable      FALSE
12350      SettingTracking      FALSE
12362      SettingEnable      TRUE
12362      SettingTracking      TRUE
12367      SettingEnable      TRUE
12367      SettingTracking      TRUE
12368      SettingEnable      TRUE
12368      SettingTracking      TRUE
12369      SettingEnable      TRUE
12369      SettingTracking      TRUE
12382      SettingEnable      FALSE
12382      SettingTracking      FALSE
12383      SettingEnable      FALSE
12383      SettingTracking      FALSE
12384      SettingEnable      TRUE
12384      SettingTracking      FALSE
12385      SettingEnable      FALSE
12385      SettingTracking      TRUE
12386      SettingEnable      TRUE
12386      SettingTracking      TRUE
12387      SettingEnable      TRUE
12387      SettingTracking      TRUE
12388      SettingEnable      FALSE
12388      SettingTracking      TRUE
12389      SettingEnable      TRUE
12389      SettingTracking      FALSE
12391      SettingEnable      TRUE
12391      SettingTracking      FALSE
12392      SettingEnable      FALSE
12392      SettingTracking      FALSE
12393      SettingEnable      FALSE
12393      SettingTracking      FALSE
12402      SettingEnable      TRUE
12402      SettingTracking      TRUE
12403      SettingEnable      TRUE
12403      SettingTracking      TRUE
12404      SettingEnable      FALSE
12404      SettingTracking      FALSE
12405      SettingEnable      FALSE
12405      SettingTracking      FALSE
12406      SettingEnable      FALSE
12406      SettingTracking      FALSE
12408      SettingEnable      TRUE
12408      SettingTracking      TRUE
12409      SettingEnable      TRUE
12409      SettingTracking      TRUE
12410      SettingEnable      FALSE
12410      SettingTracking      FALSE
12411      SettingEnable      FALSE
12411      SettingTracking      FALSE
0
Comment
Question by:bemara57
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24043781
this will do
update t
  set value = 'TRUE'
 from yourtable t
 WHERE t.value <> 'TRUE'
   and exists ( SELECT NULL FROM yourtable o WHERE o.ID = t.ID and o.value = 'TRUE' )

Open in new window

0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 24043814
UPDATE Table SET Value = 1
WHERE Name IN ('SettingEnable', 'SettingTracking')
AND ID IN (
      SELECT ID
      FROM Table
      WHERE (Name = 'SettingEnable' AND Value = 1)
            OR (Name = 'SettingTracking' AND Value = 1)
      )
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24043840
update Table
set Value = 'True'
from Table inner join
(select Id
from Table where Name = 'SettingEnable' and Value = 'TRUE'
union
select Id
from Table where Name = 'SettingTracking'  and Value = 'TRUE') as TrueIds
on Table.Id = TrueIds.Id
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:bemara57
ID: 24043856
Thanks but where is it checking for the Name being SettingTracking and SettingEnable? I have other key/value pairs in my table that are not related.
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
ID: 24043898
Mine and CGLuttrell's check the value of Name.  I assumed a 1 or 0 value for your boolean but if it needs to be spelled out then:
UPDATE Table SET Value = 'TRUE'
WHERE Name IN ('SettingEnable', 'SettingTracking')
AND ID IN (
      SELECT ID
      FROM Table
      WHERE (Name = 'SettingEnable' AND Value = 'TRUE')
            OR (Name = 'SettingTracking' AND Value = 'TRUE')
      )
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24043934
this il do:
update t

  set value = 'TRUE'

 from yourtable t

 WHERE t.value <> 'TRUE'

   and t.Name IN ('SettingEnable', 'SettingTracking')

   and exists ( SELECT NULL FROM yourtable o WHERE o.ID = t.ID and o.value = 'TRUE' and i.Name IN ('SettingEnable', 'SettingTracking')

 )

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 24044163
UPDATE tbl
SET value =
 (SELECT MAX(value)
  FROM tbl t
  WHERE t.id = tbl.id
   AND t.Name IN ('SettingEnable', 'SettingTracking') )
WHERE Name IN ('SettingEnable', 'SettingTracking') ;
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now