if statement ofr each row in select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if statement ofr each row in select statement

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-10-31 : 13:32:50

Hi All,

I want to do this for each select statement in sql

I have this table A

and columns are

col1, col2, col3, col4, col5, col6

I want to do this

If convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2

Upadte table A
set col5 = 9,
col3= 12
where col6 =9

else if col3 is null and convert(varchar(10),TableA.col5,111) =20 then

Update table A
set col6 = 10
where col5=19

How can I achieve this without declaring cursors. I need to do this for each row in the table A

Thanks,

anjali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:43:48

[code]
Update table A
set col5 =case when col6 =9 and convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2 then 9 else col5 end ,
col3=case when col6 =9 and convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2 then 12 else col3 end,
col6 =case when col5=19 and col3 is null and convert(varchar(10),TableA.col5,111) =20 then 10 else col6 end
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 13:44:23

Upadte table A
set col5 = 9,
col3= 12
where col6 =9
and convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2

Update table A
set col6 = 10
where col5=19
and col3 is null and convert(varchar(10),TableA.col5,111) =20
and not (convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

anjali5
Posting Yak Master

121 Posts

Posted - 2011-10-31 : 14:02:49

Hi,

I want to make a slight change. can you please tell me how can I take care of this. I want to update the table A col5 with another table column. basically, i want to do this

set col5 = (select max(code) from table B where Unit = tableA.unit and number = tableA.number)

If convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2

Update table A
set col5 = (select max(code) from table B where Unit = tableA.unit and number = tableA.number)

col3= 12
where col6 =9

else if col3 is null and convert(varchar(10),TableA.col5,111) =20 then

Update table A
set col6 = 10
where col5=19

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:20:07

[code]
Update a
set col5 =case when col6 =9 and convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2 then (select max(code) from table B where Unit = a.unit and number = a.number) else col5 end ,
col3=case when col6 =9 and convert(varchar(10),TableA.col1,111) = convert(varchar(10),TableA.col5,111) and convert(varchar(10),TableA.col6,111) = col2 then 12 else col3 end,
col6 =case when col5=19 and col3 is null and convert(varchar(10),TableA.col5,111) =20 then 10 else col6 end
from [table A] a

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources