update columns in a table with case

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

SQL Server 2008 Forums

Transact-SQL (2008)

update columns in a table with case

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-12-07 : 12:52:31

Hi.
table2 xx int,time1 varchar,time2 varchar, time3 varchar

row: 1,19:30,NULL,NULL

What i want to do is a case to check when the first available time row is NULL and then update it with a value.
So here it will go to time1, see it has a value and skip, goto time2 see it is null and then insert the value.
something in the lines of:


SELECT  xx,
  case when time1 is NULL  then UPDATE Table_2 set time1= '1' where xx=1                  
       when time2 is null then
UPDATE Table_2 set time2= '1' where xx=1     
       when time3 is null then
UPDATE Table_2 set time1= '1' where xx=1     
       end
from Table_2
where x=1

A VERY IMPORTANT DISTINCTION HERE is that if an update is done then i want all the other updates to be skipped, So if an update is done on time1 then time2 and time3 will not update.
If an update is done in time2 then time3 will not update.
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-12-07 : 13:34:26

What i do right now but i prefer a simpler case:



declare @isset int
set @isset =0
declare @issetrow int
set @issetrow = 0
declare @betwwen1 nvarchar(100)
declare @between2 nvarchar(100)


set @betwwen1 = NULL
set @between2 = NULL

set @betwwen1 = (select time1 from Table_2 where xx=1)
set @between2 = (select time2 from Table_2 where xx=1)

if @betwwen1 is null  begin 
update table_2 set
    time1 = (case when time1 is null and @isset =0 then '25sssss'  end) 
from Table_2 where xx =1

set @isset =1
end


set @betwwen1 = (select time1 from Table_2 where xx=1)
set @between2 = (select time2 from Table_2 where xx=1)
if @betwwen1 is not null and @between2 is null 
begin
update table_2 set  
    time2 = (case when time2 is null and @isset =0 then 'columnx'  end)
from Table_2 where xx =1
set @isset =1
end


set @betwwen1 = (select time2 from Table_2 where xx=1)
set @between2 = (select time3 from Table_2 where xx=1)
if @betwwen1 is not null and @between2 is null 
begin
update table_2 set     
    time3 = (case when time3 is null and @isset =0 then 'columny'  end)
from Table_2 where xx =1
set @isset =1
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources