We help IT Professionals succeed at work.

SQL - Update field value based on two other fields in same table.

272 Views
Last Modified: 2017-05-15
I need to update a field in a table based on two other fields in the same table.  For example Field_1 has the value of 3 and Field_2 has a value of 6, I then want Field_3 to have the values 3,6.  

If Field_1 has the value of 3 and Field_2 has a value of 0, I then want Field_3 to have the value 3.  Also this only applies if the value in Field_1 and Field_2 are null.

Any help is appreciated
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Can you explain how this is possible if you said: "this only applies if the value in Field_1 and Field_2 are null."
Well if those values are NULL how can they have the value of 3 or 6 or 0 or whatever?
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
IF OBJECT_ID('tempdb..#mytable1') IS NOT NULL DROP TABLE #mytable1


create table #mytable1
(
 FIELD_1  INT,
 FIELD_2  INT,
 FIELD_3  varchar(50),
 )

 insert into #mytable1 (FIELD_1,FIELD_2) values (3,6)
 insert into #mytable1 (FIELD_1,FIELD_2) values (1,2)
 insert into #mytable1 (FIELD_1,FIELD_2) values (4,5)
 insert into #mytable1 (FIELD_2) values (7)
 insert into #mytable1 (FIELD_1) values (8)
 insert into #mytable1 (FIELD_1) values (NULL)


 -- select FIELD_1,FIELD_2,FIELD_3 from #mytable1 where (isnull(FIELD_1,'')<>'' AND isnull(FIELD_2,'')<>'')

 UPDATE #mytable1 set FIELD_3 = cast(FIELD_1 as varchar)+','+cast(FIELD_2 as varchar) where (isnull(FIELD_1,'')<>'' AND isnull(FIELD_2,'')<>'')
 
 select FIELD_1,FIELD_2,FIELD_3 from #mytable1 

Open in new window

SAMPLE

Author

Commented:
Good point - should not be a factor

Author

Commented:
Jeff -So it looks like I can just use this portion - correct? - I have to test it on another computer far away.

 UPDATE #mytable1 set FIELD_3 = cast(FIELD_1 as varchar)+','+cast(FIELD_2 as varchar) where (isnull(FIELD_1,'')<>'' AND isnull(FIELD_2,'')<>'')
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Please try this version:
UPDATE TableName
SET Field_3 = 
    CASE
        WHEN ISNULL(Field_1,0)  = 0 THEN ''
        ELSE CAST(Field_1 AS VARCHAR)
    END 
    + 
    CASE
        WHEN ISNULL(Field_1,0)  = 0 OR ISNULL(Field_2,0) = 0 THEN ''
        ELSE ',' 
    END 
    + 
    CASE
        WHEN ISNULL(Field_2,0)  = 0 THEN ''
        ELSE CAST(Field_2 AS VARCHAR)
    END 

Open in new window

Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
Yes.

UPDATE #mytable1 set FIELD_3 = cast(FIELD_1 as varchar)+','+cast(FIELD_2 as varchar) where (isnull(FIELD_1,'')<>'' AND isnull(FIELD_2,'')<>'')

Open in new window

Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
I may have misunderstood the requirements.  I thought that you only wanted the update if FIELD_1 and FIELD_2 are not null.

The SQL from @Vitor Montalvão gives this..

SAMPLE

Author

Commented:
I do like both versions.  I did not explain the problem correctly -
 If Field_1 has the value of 1 then Field_3 should have a value of 3 inserted.
If Field_1 has the value of 1 and Field_2 has a value of 1 then Field_3 should have 3,6
If Field_2 has the value of 1 then Field_3 should have the value of 6

Does that makes sense?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
So Field_1 and Field_2 can only have binary values (0, 1)?

Author

Commented:
Yes - data type is bit
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Vitor - That one works great - are there any objections for you to have the points?

Author

Commented:
Great thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.