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
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
Last Comment
Andy Green
ASKER CERTIFIED SOLUTION
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.
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
UPDATE - No can't do it that way as the data comes from multiple tables.
Functions next
A
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;
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.
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.
ASKER
Have this working now with a function, but did use the if exists inside.
Thanks guys.
Andy
Thanks guys.
Andy
A