How to Update Multiple Column from Single column sql server 2005 1

[macsql1]

Hi,

I have 5 column in a table, however one column is updated with recored and rest Null value.

Tabletest:-
ONE TWO THREE four five
1
2
3
4
5

My condition is, if record value of column one is 2 then i want to Update column TWO with 'True' if value of column two is 3 then i want to Update column THREE with 'True'.... else null

Thanks in advance

Mac

 

[bborissov]

And how you supposed to have two different types in ONE column?

if record value of column one is 2 then I want to Update column TWO with 'True'
if value of column two is 3 then i want to Update column THREE with 'True'.... else null
[/code]

So how do you have TRUE and 3 in the same column (2)???

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[DClyde]

I am not really sure I am following your question.

In your example if Column "One" is 2 then you want to set Column "Two" to True.

But if Column "Two" is 3 then you want to update Column "Three" to True else NULL.

Column "Two" in your example is both of type int and of type bit?

Regardless, one option you may want to look at is Triggers so that you can set one column based on another whenever the data is updated/inserted instead of running a seperate query at a later time if it would benefit you to always have the other columns properly calculated from the other. If it is more a process of "ok, we already verified this" then you probably want to leave it as the seperate query.

 

[macsql1]

Thanks for you update, This is only one time update on database.

Error with Example
update tabletest
set two =
case when one = 2 then 'True',
case when two = 3 then 'True' end
from tabletest

 

[bborissov]

Could you post the table structure?
Especially Two type?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[macsql1]

Sure, Appreciate your prompt response.

For Columns: TWO, Three, Four, Five
Data Type = bit
allow nulls: Yes

For Column: One
Data type = int
allow nulls: Yes

mac

 

[bborissov]

Then how you suppose to have 3 in that column?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[macsql1]

Ohh, its my mistake,

Now the structure is like below, How we can minimize the query. i want to write single UPDATE command which will all columns

update tabletest
set two =
case when one = 2 then 'True' end
from tabletest

update tabletest
set three =
case when one = 3 then 'True' end
from tabletest

update tabletest
set four =
case when one = 4 then 'True' end
from tabletest

 

[bborissov]

O! Now it make sense

UPDATE TableTest
       SET Two    = CASE WHEN One = 2
                         THEN 1
                    ELSE Two END,
        SET Three = CASE WHEN One = 3
                         THEN 1
                 ELSE Three END,
        SET Four = CASE WHEN One = 4
                         THEN 1
                 ELSE Four END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[macsql1]

Hi, Got this error during Parse, Something missing ?

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.

UPDATE TableTest
SET Two = CASE WHEN One = 2
THEN 1
ELSE Two END,
SET Three = CASE WHEN One = 3
THEN 1
ELSE Three END,
SET Four = CASE WHEN One = 4
THEN 1
ELSE Four END
from TableTest

 

[bborissov]

O! you should use SET only once, Copy & Paste error.
BTW There is no need of FROM clause here unless other tables are involved.

UPDATE TableTest
       SET Two    = CASE WHEN One = 2
                         THEN 1
                    ELSE Two END,
           Three  = CASE WHEN One = 3
                         THEN 1
                    ELSE Three END,
           Four   = CASE WHEN One = 4
                         THEN 1
                    ELSE Four END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.