Avatar of Andy Green
Andy Green
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQL set flag if any 1 row has certain data

Hi

I have an sql query that returns several rows of data. I need to set a flag on all rows if any one row has certain data.

Example

Declare  @myTable TABLE(dID int, Data varchar(25),  Flag bit)

  Insert into @myTable Select 1,'1', 0
  Insert into @myTable Select 2,'2', 0
  Insert into @myTable Select 3,'3', 0
  Insert into @myTable Select 4,'4', 0
  Insert into @myTable Select 5,'5', 0
  Insert into @myTable Select 6,'6', 0
  Insert into @myTable Select 7,'7', 0
  Insert into @myTable Select 8,'8', 0
  Insert into @myTable Select 9,'9', 0
  Insert into @myTable Select 10,'10', 0

Select * from @myTable

If any row has the value of 5 in the data column, that the flag for all rows is to be set to 1.

I'm using SQL server 2008

Andy
SQL

Avatar of undefined
Last Comment
Andy Green

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andy Green

ASKER
Does this mean there is no set based way to do this?

A
Andy Green

ASKER
I'm investigating computed columns now, see if I can dynamically store the data that way.

UPDATE - No can't do it that way as the data comes from multiple tables.

Functions next

A
ste5an

Does this mean there is no set based way to do this?
Correct, flags are not a concept in relational databases. Especially as it is a simple EXISTS():
DECLARE @myTable TABLE (
    ID INT PRIMARY KEY ,
    Data VARCHAR(25) ,
    Flag BIT
);

INSERT INTO @myTable
VALUES ( 1, '1', 0 ) ,
       ( 2, '2', 0 ) ,
       ( 3, '3', 0 ) ,
       ( 4, '4', 0 ) ,
       ( 5, '5', 0 ) ,
       ( 6, '6', 0 ) ,
       ( 7, '7', 0 ) ,
       ( 8, '8', 0 ) ,
       ( 9, '9', 0 ) ,
       ( 10, '10', 0 );

SELECT * ,
       IIF(EXISTS ( SELECT * FROM @myTable MT2 WHERE MT2.Data = '5' ), 1, 0) AS WhatEver
FROM   @myTable MT;

SELECT *
FROM   @myTable MT
       CROSS APPLY ( SELECT IIF(EXISTS ( SELECT * FROM @myTable MT2 WHERE MT2.Data = '5' ), 1, 0) AS WhatEver ) Q;

Open in new window


I'm investigating computed columns now, see if I can dynamically store the data that way.
Computed columns can only reference values of the current (their) row.

In a clean relational model, this is just a separate table, where you you would have a row, when you condition is true otherwise it would be removed. E.g. by a trigger or sproc.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sameer2010

Or you can write a trigger to update all rows in one or more tables if any of the input values are inserted/updated/deleted.
Andy Green

ASKER
Have this working now with a function, but did use the if exists inside.

Thanks guys.

Andy