Value of column dependent on the values of others. How can I do it . Please help !

Question

Post reply

Value of column dependent on the values of others. How can I do it . Please help !

dragosgrigs

Valued Member

Points: 53
More actions
December 14, 2012 at 6:49 am

#271640

Hello , this is the first time i'm posting here, but this time i really need your help.
I need to write a query to update a table so the value of a specific column is dependent of the values of all other columns in the row.
EX
table 1
Val1 | Val2 | Val3 | Val4|...
e | b | . | u |
I need the Val4 value to be 'u' is there's no column with '.' and 'U' is there is. The table i'm talking about has a lot of columns. Please help ASAP
Thank you
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

update tbl

set u = case when val1 = '.' or val2 = '.' or val3 = '.' then 'U' else 'u' end

Cursors never.
DTS - only when needed and never to control.

dragosgrigs Valued Member Points: 53 More actions · Answer 2

Thank you for this but the problem is that I'm working with a table that has over 80 columns and I was thinking if there's an different, easyer way to do it.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

dragosgrigs (12/14/2012)
Thank you for this but the problem is that I'm working with a table that has over 80 columns and I was thinking if there's an different, easyer way to do it.

Nope. You are going to have to look at each column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

If you execute this, it's at your own risk. Do it in a test environment first and make sure you understand what is going on.

DECLARE @Table_Name VARCHAR(20) = 'table 1', @Update_Column VARCHAR(20) = 'Val4', @sql NVARCHAR(MAX);

SELECT @sql = 'UPDATE '+QUOTENAME(@Table_Name)+CHAR(13)+CHAR(10)+'SET '+QUOTENAME(@Update_Column)+' = CASE WHEN ' +

STUFF((SELECT ' OR ' +val

FROM (SELECT name+'='+CHAR(39)+'.'+CHAR(39)

FROM sys.columns

WHERE object_id = OBJECT_ID(@Table_Name)

AND name <> @Update_Column

)a(val)

FOR XML PATH(''), TYPE

).value('.','NVARCHAR(MAX)'),1,4,'')+' THEN '+CHAR(39)+'U'+CHAR(39)+' ELSE '+CHAR(39)+'u'+CHAR(39)+' END';

EXECUTE sp_executesql @sql;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You could do it a little easier with some string manipulation.

;with cte (val1, val2, val3)

as

(

select 'asdf', 'qwer', 'rtyu' union all

select 'as.df', 'yt', 'hhg' union all

select 'weret', '99t4.rtkg9', ''

)

select case when CHARINDEX('.', val1 + val2 + val3) > 0 then 'U' else 'u' end

from cte

If your columns allow NULL you would need to wrap each column with an ISNULL(val1, '').

That is at least a little less typing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Richard Warr SSCertifiable Points: 6967 More actions · Answer 6

If the table has a key column another option would be to UNPIVOT it into a temp table and then do your update based on that. For example you could create a table called #AllColumns which looks like:

KeyVal ColVal

123 asdf

123 qwe

123 rt.y

so there is one row for each row/column combination in your current table.

Then you just do

UPDATE FirstTable SET LastCol =

CASE WHEN EXISTS (

SELECT 1 FROM #AllColumns

WHERE KeyVal = FirstTable.KeyVal

AND CHARINDEX('.',ColVal) > 0)

THEN 'U' ELSE 'u' END;

Hope that makes some sort of sense!

dragosgrigs Valued Member Points: 53 More actions · Answer 7

dragosgrigs

Valued Member

Points: 53

December 14, 2012 at 7:51 am

#1568714

Thank you ! This worked. Much appreciated it

dragosgrigs Valued Member Points: 53 More actions · Answer 8

Thank you all for the prompt responses. Damn, you guys are good

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

dragosgrigs (12/14/2012)
Thank you all for the prompt responses. Damn, you guys are good

I have a question, though. What do all of these columns contain? Do they contain just 1 character each, for example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 10

And don't forget about computed columns. You can make this a computed column, so you don't have to have the generating code in a trigger. Since it's a fair amount of overhead to generate, and it's a very small value, I suggest PERSISTing the computed column so SQL doesn't have to recompute it for every use.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

A bit late to the party, but this cries out for a bit of "bad practice"...(SELECT *)

-- drop the temporary table if it already exists

if object_id('tempdb..#sample') is not null

drop table #sample;

-- create some sample data

select 'a' col1,'b' col2,'.' col3,'d' col4,'e' col5,'f' col6

into #sample

union all

select 'a','.','c','d','e','f' union all

select '.','.','.','.','.','f' union all

select 'a','b','c','d','e','f' union all

select 'a','b','c','.','e','f'

-- perform the update

update sample

set col6 = case charindex('.',(select sample.* for xml path('a'))) when 0 then 'u' else 'u' end

output inserted.*

from #sample as sample;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 12

Minor mistake in Magoo's excellent code has been corrected in the following...

-- drop the temporary table if it already exists
if object_id('tempdb..#sample') is not null
drop table #sample;
-- create some sample data
select 'a' col1,'b' col2,'.' col3,'d' col4,'e' col5,'f' col6
into #sample
union all
select 'a','.','c','d','e','f' union all
select '.','.','.','.','.','f' union all
select 'a','b','c','d','e','f' union all
select 'a','b','c','d','e','f' union all
select 'a','b','c','.','e','f'
-- perform the update
update sample
set col6 = case charindex('.',(select sample.* for xml path('a'))) when 0 then col6 else 'u' end
output inserted.*
from #sample as sample;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

Jeff Moden (12/16/2012)
Minor mistake in Magoo's excellent code has been corrected in the following...

Thanks for the kind words Jeff, and thanks for spotting my mistake.

I am not sure that col6 is right in there in place of one of my 'u's

(which came from a quick ctrl-a, shift-ctrl-l before posting the code - doh!)

but admit it could be - the OP was a bit unclear about that.

I based my use of 'u' and 'U' as the two possibilities on the other solutions that do the same...

Richard Warr
THEN 'U' ELSE 'u' END

Sean Lange
then 'U' else 'u'

Cadavre
' THEN '+CHAR(39)+'U'+CHAR(39)+' ELSE '+CHAR(39)+'u'

So, mine should be

-- drop the temporary table if it already exists

if object_id('tempdb..#sample') is not null

drop table #sample;

-- create some sample data

select 'a' col1,'b' col2,'.' col3,'d' col4,'e' col5,'f' col6

into #sample

union all

select 'a','.','c','d','e','f' union all

select '.','.','.','.','.','f' union all

select 'a','b','c','d','e','f' union all

select 'a','b','c','.','e','f'

-- perform the update

update sample

set col6 = case charindex('.',(select sample.* for xml path('a'))) when 0 then 'u' else 'U' end

output inserted.*

from #sample as sample;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

Yeah... you're right. I forgot the OP wanted 'u' and 'U'. Your good code is spot on now. Thanks for the feedback, Magoo.

And, I'll say it again... excellent code that will handle any number of columns. Long live "SELECT *". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)