How to use functions to update columns?

• patelxx

  SSC Eights!

  Points: 853

  More actions

  June 30, 2018 at 2:00 am

  #353262

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  July 1, 2018 at 5:06 am

  #1995940

  patelxx - Saturday, June 30, 2018 2:00 AM

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

  
select ID,
CODE1=case when coalesce(CODE1,CODE2,CODE3,CODE4,CODE5) IS NULL OR CODE1 IS NULL then 999 else CODE1 end,
CODE2=case when CODE1 IS NULL and CODE2 IS NOT NULL then 999 else CODE2 end,
CODE3=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NULL then 999 else CODE3 end,
CODE4=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NOT NULL and CODE4 IS NULL then 999 else CODE4 end,
CODE5=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NOT NULL and CODE4 IS NOT NULL and CODE5 IS NULL then 999 else CODE5 end
from Number_Codes


  Saravanan

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  July 1, 2018 at 9:45 am

  #1995943

  patelxx - Saturday, June 30, 2018 2:00 AM

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

  Here is one way of doing. Please provide the DDL and DML statements. Here i have constructed a tiny example based on what i could come up with

  
CREATE TABLE Number_Codes(
ID INT,
CODE1 INT NULL, --say this is populated with code: 7062
CODE2 INT NULL, --Only this one needs to be populated with code: 999
CODE3 INT NULL,
CODE4 INT NULL,
CODE5 INT NULL
);

  insert into number_codes values(7062,null,1,2,3,4);
insert into number_codes values(7063,1,1,null,3,4);
insert into number_codes values(7064,1,1,2,3,4);
insert into number_codes values(7065,1,3,null,2,null);
insert into number_codes values(7066,null,null,null,null,null);
insert into number_codes values(7067,1,21,null,null,null);

  select * from (
    select row_number() over(partition by id order by col2,col1) as rnk
      ,x.id,x.col1,x.col2
    from (select id,'code1' as col1,code1 as col2 from number_codes
      union all
      select id,'code2',code2 from number_codes
      union all
      select id,'code3',code3 from number_codes
      union all
      select id,'code4',code4 from number_codes
      union all
      select id,'code5',code5 from number_codes
      )x 
      )y
    where y.rnk=1;

    
  update a
     set a.code1=case when b.col1='code1' and b.col2 is null then 999 else a.code1 end,
         a.code2=case when b.col1='code2' and b.col2 is null then 999 else a.code2 end,
         a.code3=case when b.col1='code3' and b.col2 is null then 999 else a.code3 end,
         a.code4=case when b.col1='code4' and b.col2 is null then 999 else a.code4 end,
         a.code5=case when b.col1='code5' and b.col2 is null then 999 else a.code5 end
    from number_codes as a
    join (select * from (
           select row_number() over(partition by x.id order by x.col2,x.col1) as rnk
                 ,x.id,x.col1,x.col2
           from (select id,'code1' as col1,code1 as col2 from number_codes
                 union all
                 select id,'code2',code2 from number_codes
                 union all
                 select id,'code3',code3 from number_codes
                 union all
                 select id,'code4',code4 from number_codes
                 union all
                 select id,'code5',code5 from number_codes
                )x 
               )y
          where y.rnk=1
         )b
       on a.id=b.id;

  --Final Output...   
  select *
    from number_codes;


• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  July 2, 2018 at 4:39 am

  #1995977

  patelxx - Saturday, June 30, 2018 2:00 AM

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

  Why do you need u function for that?
  To update a single column you can easily write an UPDATE statement. Adjust the WHERE in this statement to update only the rows you need.

  UPDATE Number_Codes
SET CODE2 = 999
WHERE CODE2 IS NULL

  The above code will update ALL the rows in the table where the value in column CODE2 is NULL.

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  July 2, 2018 at 5:35 am

  #1995982

  UPDATE Number_Codes
SET
   CODE1 = ISNULL(CODE1, 999)
,  CODE2 = IIF(CODE1 IS NOT NULL AND CODE2 IS NULL, 999, CODE2)
,  CODE3 = IIF((CODE1 + CODE2) IS NOT NULL AND CODE3 IS NULL, 999, CODE3)
,  CODE4 = IIF((CODE1 + CODE2 + CODE3) IS NOT NULL AND CODE4 IS NULL, 999, CODE4)
,  CODE5 = IIF((CODE1 + CODE2 + CODE3 + CODE4) IS NOT NULL AND CODE5 IS NULL, 999, CODE5);

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 2, 2018 at 6:27 am

  #1995987

  Functions are meant to return values. Functions are NOT meant to make changes to a database.
  You might be trying to create a Stored Procedure. Understanding the difference between both is a basic step to prevent losing time trying to do incorrect things.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 2, 2018 at 8:18 am

  #1996017

  patelxx - Saturday, June 30, 2018 2:00 AM

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

  I'm also concerned that this might be a rather bad idea.   The reason you use a relational database is to gain the benefits thereof, and having any single column have different meanings within every row of a given table is a pretty significant way to do a very wrong thing.   You just don't use databases that way,   Columns are always supposed to have the exact same meaning in the same table, for a given value, all the time, always.   Start breaking that rule and the ability get any useful reports out of such a mess rather quickly disappears.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• patelxx

  SSC Eights!

  Points: 853

  More actions

  July 4, 2018 at 6:49 am

  #1996295

  saravanatn - Sunday, July 1, 2018 5:06 AM

  patelxx - Saturday, June 30, 2018 2:00 AM

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

  
select ID,
CODE1=case when coalesce(CODE1,CODE2,CODE3,CODE4,CODE5) IS NULL OR CODE1 IS NULL then 999 else CODE1 end,
CODE2=case when CODE1 IS NULL and CODE2 IS NOT NULL then 999 else CODE2 end,
CODE3=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NULL then 999 else CODE3 end,
CODE4=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NOT NULL and CODE4 IS NULL then 999 else CODE4 end,
CODE5=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NOT NULL and CODE4 IS NOT NULL and CODE5 IS NULL then 999 else CODE5 end
from Number_Codes


  Hi Mate,

  Please can you explain what this line is actually doing: 
  coalesce(CODE1,CODE2,CODE3,CODE4,CODE5) IS NULL

  Cheers
  Ravi
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 9, 2018 at 1:53 pm

  #1996856

  patelxx - Wednesday, July 4, 2018 6:49 AM

  saravanatn - Sunday, July 1, 2018 5:06 AM

  patelxx - Saturday, June 30, 2018 2:00 AM

  I am a beginner with using functions, I need to update only one column (which is the FIRST null it finds) with a code (999) using a function.  The table structure is as follows:

  CREATE TABLE Number_Codes(
       ID INT,
       CODE1 INT NULL,   --say this is populated with code:   7062
       CODE2 INT NULL,   --Only this one needs to be populated with code: 999
       CODE3 INT NULL,
       CODE4 INT NULL,
       CODE5 INT NULL
  )
  So how would I write a function which will update ONLY one column (CODE2) which is NULL with the code 999.

  
select ID,
CODE1=case when coalesce(CODE1,CODE2,CODE3,CODE4,CODE5) IS NULL OR CODE1 IS NULL then 999 else CODE1 end,
CODE2=case when CODE1 IS NULL and CODE2 IS NOT NULL then 999 else CODE2 end,
CODE3=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NULL then 999 else CODE3 end,
CODE4=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NOT NULL and CODE4 IS NULL then 999 else CODE4 end,
CODE5=case when CODE1 IS NOT NULL and CODE2 IS NOT NULL and CODE3 IS NOT NULL and CODE4 IS NOT NULL and CODE5 IS NULL then 999 else CODE5 end
from Number_Codes


  Hi Mate,

  Please can you explain what this line is actually doing: 
  coalesce(CODE1,CODE2,CODE3,CODE4,CODE5) IS NULL

  Cheers
  Ravi
  

  That's a check to see if all 5 of those columns are NULL, as the COALESCE function will return a single value, taken from the first non-NULL value in the list.   If there are no non-NULL values in the list, it will return a NULL of the data type of the first element in the list.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)