Swap values of multiple columns with a CASE statement  
Author Message
AnthonyThomas





PostPosted: Mon Jun 14 11:54:46 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement

Hi all. Help for a newbie would be greatly appreciated.

I'm trying to update existing data that came from an Access database, and
when I'm finished, none of the columns will be allowed to have null values.
But, the existing data does contain nulls.

So, within the same table, I need to swap the values of 4 columns with the
values of 4 other columns, unless the 4 other columns contain null values.
When they do, they need to simply be filled with the values of the first 4
columns.

CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid int
NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)

INSERT INTO d VALUES
(123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
INSERT INTO d VALUES
(345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)

So, I need:

id type year nmct cid
ctype cyear cnmct
123456789 15 199612 ABC 234567890 20 199712
DEF
345678901 10 200012 GHI NULL NULL NULL
NULL

to become:

234567890 20 199712 DEF 123456789 15 199612
ABC
345678901 10 200012 GHI 345678901 10 200012
GHI

I can do the swap when all the fields have values. But, haven't figured out
how to handle the nulls. I have researched SET...CASE...WHEN. But
everything I've found updates only a single column. Any suggestions?

In the existing data, if one of the columns has a null value, all 4 of the
columns will have null values. In other words, cid can't be null and ctype
contain a value. However, I anticipate future situations where any one of
the fields may contain null values. So, if you can advise how to handle it
in such a case, that would be great.

Thanks!

Iris

SQL Server44  
 
 
Steve





PostPosted: Mon Jun 14 11:54:46 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement



>Hi all. Help for a newbie would be greatly appreciated.
>
>I'm trying to update existing data that came from an Access database, and
>when I'm finished, none of the columns will be allowed to have null values.
>But, the existing data does contain nulls.
>
>So, within the same table, I need to swap the values of 4 columns with the
>values of 4 other columns, unless the 4 other columns contain null values.
>When they do, they need to simply be filled with the values of the first 4
>columns.
>
>CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid int
>NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
>
>INSERT INTO d VALUES
>(123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
>INSERT INTO d VALUES
>(345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
>
>So, I need:
>
>id type year nmct cid
>ctype cyear cnmct
>123456789 15 199612 ABC 234567890 20 199712
>DEF
>345678901 10 200012 GHI NULL NULL NULL
>NULL
>
>to become:
>
>234567890 20 199712 DEF 123456789 15 199612
>ABC
>345678901 10 200012 GHI 345678901 10 200012
>GHI
>
>
It looks a lot like you have repeating groups in your table, but unless
you want to change the structure, try something like:

update d set
id = coalesce(cid, id),
type = coalesce(ctype, type),
year = coalesce(cyear, year),
nmct = coalesce(cnmct, nmct),
cid = coalesce(id, cid),
ctype = coalesce(type, ctype),
cyear = coalesce(year, cyear),
cnmct = coalesce(nmct, cnmct)
where
cid is null or
ctype is null or
cyear is null or
cnmct is null

Steve Kass
Drew University

>I can do the swap when all the fields have values. But, haven't figured out
>how to handle the nulls. I have researched SET...CASE...WHEN. But
>everything I've found updates only a single column. Any suggestions?
>
>In the existing data, if one of the columns has a null value, all 4 of the
>columns will have null values. In other words, cid can't be null and ctype
>contain a value. However, I anticipate future situations where any one of
>the fields may contain null values. So, if you can advise how to handle it
>in such a case, that would be great.
>
>Thanks!
>
>Iris
>
>
>
>

 
 
Anith





PostPosted: Mon Jun 14 11:55:13 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement You can do:

UPDATE d
SET cid = id,
ctype = type,
cyear = "year",
cnmct = nmct,
id = COALESCE( cid, id ),
type = COALESCE( ctype, type ),
"year" = COALESCE( cyear, "year" ),
nmct = COALESCE( cnmct, nmct )
WHERE ...

Note that you should also care about not using keywords and inbuilt function
names as column names.

--
Anith


 
 
Adam





PostPosted: Mon Jun 14 11:57:57 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement Try:

UPDATE YourTable
SET cid = CASE WHEN cid IS NULL THEN id ELSE cid END,
SET ctype = CASE WHEN ctype IS NULL THEN type ELSE ctype END,
SET cyear = CASE WHEN cyear IS NULL THEN year ELSE cyear END,
SET cnmct = CASE WHEN cnmct IS NULL THEN nmct ELSE cnmct END




> Hi all. Help for a newbie would be greatly appreciated.
>
> I'm trying to update existing data that came from an Access database, and
> when I'm finished, none of the columns will be allowed to have null
values.
> But, the existing data does contain nulls.
>
> So, within the same table, I need to swap the values of 4 columns with the
> values of 4 other columns, unless the 4 other columns contain null values.
> When they do, they need to simply be filled with the values of the first 4
> columns.
>
> CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid int
> NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
>
> INSERT INTO d VALUES
> (123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
> INSERT INTO d VALUES
> (345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
>
> So, I need:
>
> id type year nmct cid
> ctype cyear cnmct
> 123456789 15 199612 ABC 234567890 20
199712
> DEF
> 345678901 10 200012 GHI NULL NULL NULL
> NULL
>
> to become:
>
> 234567890 20 199712 DEF 123456789 15 199612
> ABC
> 345678901 10 200012 GHI 345678901 10 200012
> GHI
>
> I can do the swap when all the fields have values. But, haven't figured
out
> how to handle the nulls. I have researched SET...CASE...WHEN. But
> everything I've found updates only a single column. Any suggestions?
>
> In the existing data, if one of the columns has a null value, all 4 of the
> columns will have null values. In other words, cid can't be null and
ctype
> contain a value. However, I anticipate future situations where any one of
> the fields may contain null values. So, if you can advise how to handle
it
> in such a case, that would be great.
>
> Thanks!
>
> Iris
>
>


 
 
Iris





PostPosted: Mon Jun 14 12:53:13 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement


> Hi all. Help for a newbie would be greatly appreciated.
>
> I'm trying to update existing data that came from an Access database, and
> when I'm finished, none of the columns will be allowed to have null
values.
> But, the existing data does contain nulls.
>
> So, within the same table, I need to swap the values of 4 columns with the
> values of 4 other columns, unless the 4 other columns contain null values.
> When they do, they need to simply be filled with the values of the first 4
> columns.
>
> CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid int
> NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
>
> INSERT INTO d VALUES
> (123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
> INSERT INTO d VALUES
> (345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
>
> So, I need:
>
> id type year nmct cid
> ctype cyear cnmct
> 123456789 15 199612 ABC 234567890 20
199712
> DEF
> 345678901 10 200012 GHI NULL NULL NULL
> NULL
>
> to become:
>
> 234567890 20 199712 DEF 123456789 15 199612
> ABC
> 345678901 10 200012 GHI 345678901 10 200012
> GHI
>
> I can do the swap when all the fields have values. But, haven't figured
out
> how to handle the nulls. I have researched SET...CASE...WHEN. But
> everything I've found updates only a single column. Any suggestions?
>
> In the existing data, if one of the columns has a null value, all 4 of the
> columns will have null values. In other words, cid can't be null and
ctype
> contain a value. However, I anticipate future situations where any one of
> the fields may contain null values. So, if you can advise how to handle
it
> in such a case, that would be great.
>
> Thanks!
>
> Iris
>
>


 
 
Iris





PostPosted: Mon Jun 14 12:58:00 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement Thanks much for all the responses. You are correct Anith -- I will fix my
column names.

I tried all the suggestions, and was successful filling the null values, but
it didn't perform the swap on those that already had data in the columns. I
think I was unclear in my original message in that I was attempting to do
both at the same time, instead of in two steps. But, from the responses,
I'm thinking that it has to be done in two steps. Is that right?

Thanks again!



> Hi all. Help for a newbie would be greatly appreciated.
>
> I'm trying to update existing data that came from an Access database, and
> when I'm finished, none of the columns will be allowed to have null
values.
> But, the existing data does contain nulls.
>
> So, within the same table, I need to swap the values of 4 columns with the
> values of 4 other columns, unless the 4 other columns contain null values.
> When they do, they need to simply be filled with the values of the first 4
> columns.
>
> CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid int
> NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
>
> INSERT INTO d VALUES
> (123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
> INSERT INTO d VALUES
> (345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
>
> So, I need:
>
> id type year nmct cid
> ctype cyear cnmct
> 123456789 15 199612 ABC 234567890 20
199712
> DEF
> 345678901 10 200012 GHI NULL NULL NULL
> NULL
>
> to become:
>
> 234567890 20 199712 DEF 123456789 15 199612
> ABC
> 345678901 10 200012 GHI 345678901 10 200012
> GHI
>
> I can do the swap when all the fields have values. But, haven't figured
out
> how to handle the nulls. I have researched SET...CASE...WHEN. But
> everything I've found updates only a single column. Any suggestions?
>
> In the existing data, if one of the columns has a null value, all 4 of the
> columns will have null values. In other words, cid can't be null and
ctype
> contain a value. However, I anticipate future situations where any one of
> the fields may contain null values. So, if you can advise how to handle
it
> in such a case, that would be great.
>
> Thanks!
>
> Iris
>
>


 
 
Adam





PostPosted: Mon Jun 14 13:08:21 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement No, you can swap at the same time:

<rest of SETs>
...
SET cnmct = nmct,
SET nmct = CASE WHEN cnmct IS NOT NULL THEN cnmct END

* due to the swap requirement and the NULL requirement, I believe that cnmct
will always end up with the value of nmct, so no logic is necessary there.




> Thanks much for all the responses. You are correct Anith -- I will fix my
> column names.
>
> I tried all the suggestions, and was successful filling the null values,
but
> it didn't perform the swap on those that already had data in the columns.
I
> think I was unclear in my original message in that I was attempting to do
> both at the same time, instead of in two steps. But, from the responses,
> I'm thinking that it has to be done in two steps. Is that right?
>
> Thanks again!
>


> > Hi all. Help for a newbie would be greatly appreciated.
> >
> > I'm trying to update existing data that came from an Access database,
and
> > when I'm finished, none of the columns will be allowed to have null
> values.
> > But, the existing data does contain nulls.
> >
> > So, within the same table, I need to swap the values of 4 columns with
the
> > values of 4 other columns, unless the 4 other columns contain null
values.
> > When they do, they need to simply be filled with the values of the first
4
> > columns.
> >
> > CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid
int
> > NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
> >
> > INSERT INTO d VALUES
> > (123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
> > INSERT INTO d VALUES
> > (345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
> >
> > So, I need:
> >
> > id type year nmct cid
> > ctype cyear cnmct
> > 123456789 15 199612 ABC 234567890 20
> 199712
> > DEF
> > 345678901 10 200012 GHI NULL NULL
NULL
> > NULL
> >
> > to become:
> >
> > 234567890 20 199712 DEF 123456789 15 199612
> > ABC
> > 345678901 10 200012 GHI 345678901 10 200012
> > GHI
> >
> > I can do the swap when all the fields have values. But, haven't figured
> out
> > how to handle the nulls. I have researched SET...CASE...WHEN. But
> > everything I've found updates only a single column. Any suggestions?
> >
> > In the existing data, if one of the columns has a null value, all 4 of
the
> > columns will have null values. In other words, cid can't be null and
> ctype
> > contain a value. However, I anticipate future situations where any one
of
> > the fields may contain null values. So, if you can advise how to handle
> it
> > in such a case, that would be great.
> >
> > Thanks!
> >
> > Iris
> >
> >
>
>


 
 
Steve





PostPosted: Mon Jun 14 14:49:13 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement Iris,

If you delete the WHERE clause from my query, it should do what you want.

SK



>Thanks much for all the responses. You are correct Anith -- I will fix my
>column names.
>
>I tried all the suggestions, and was successful filling the null values, but
>it didn't perform the swap on those that already had data in the columns. I
>think I was unclear in my original message in that I was attempting to do
>both at the same time, instead of in two steps. But, from the responses,
>I'm thinking that it has to be done in two steps. Is that right?
>
>Thanks again!
>


>
>
>>Hi all. Help for a newbie would be greatly appreciated.
>>
>>I'm trying to update existing data that came from an Access database, and
>>when I'm finished, none of the columns will be allowed to have null
>>
>>
>values.
>
>
>>But, the existing data does contain nulls.
>>
>>So, within the same table, I need to swap the values of 4 columns with the
>>values of 4 other columns, unless the 4 other columns contain null values.
>>When they do, they need to simply be filled with the values of the first 4
>>columns.
>>
>>CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid int
>>NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
>>
>>INSERT INTO d VALUES
>>(123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
>>INSERT INTO d VALUES
>>(345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
>>
>>So, I need:
>>
>>id type year nmct cid
>>ctype cyear cnmct
>>123456789 15 199612 ABC 234567890 20
>>
>>
>199712
>
>
>>DEF
>>345678901 10 200012 GHI NULL NULL NULL
>>NULL
>>
>>to become:
>>
>>234567890 20 199712 DEF 123456789 15 199612
>>ABC
>>345678901 10 200012 GHI 345678901 10 200012
>>GHI
>>
>>I can do the swap when all the fields have values. But, haven't figured
>>
>>
>out
>
>
>>how to handle the nulls. I have researched SET...CASE...WHEN. But
>>everything I've found updates only a single column. Any suggestions?
>>
>>In the existing data, if one of the columns has a null value, all 4 of the
>>columns will have null values. In other words, cid can't be null and
>>
>>
>ctype
>
>
>>contain a value. However, I anticipate future situations where any one of
>>the fields may contain null values. So, if you can advise how to handle
>>
>>
>it
>
>
>>in such a case, that would be great.
>>
>>Thanks!
>>
>>Iris
>>
>>
>>
>>
>
>
>
>

 
 
Iris





PostPosted: Mon Jun 14 15:11:17 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement I was mistaken earlier when I said this worked for the null values but not
the swap. This works perfectly just as written. It didn't work for me
because I inserted WHERE criteria and should not have. Apologies and thanks
again for the help!



> You can do:
>
> UPDATE d
> SET cid = id,
> ctype = type,
> cyear = "year",
> cnmct = nmct,
> id = COALESCE( cid, id ),
> type = COALESCE( ctype, type ),
> "year" = COALESCE( cyear, "year" ),
> nmct = COALESCE( cnmct, nmct )
> WHERE ...
>
> Note that you should also care about not using keywords and inbuilt
function
> names as column names.
>
> --
> Anith
>
>


 
 
Iris





PostPosted: Mon Jun 14 15:31:09 CDT 2004 Top

SQL Server Developer >> Swap values of multiple columns with a CASE statement Yes it does. I think you and I posted at the same time that it was my WHERE
criteria that was messing me up. Thanks again!



> Iris,
>
> If you delete the WHERE clause from my query, it should do what you
want.
>
> SK
>

>
> >Thanks much for all the responses. You are correct Anith -- I will fix
my
> >column names.
> >
> >I tried all the suggestions, and was successful filling the null values,
but
> >it didn't perform the swap on those that already had data in the columns.
I
> >think I was unclear in my original message in that I was attempting to do
> >both at the same time, instead of in two steps. But, from the responses,
> >I'm thinking that it has to be done in two steps. Is that right?
> >
> >Thanks again!
> >


> >
> >
> >>Hi all. Help for a newbie would be greatly appreciated.
> >>
> >>I'm trying to update existing data that came from an Access database,
and
> >>when I'm finished, none of the columns will be allowed to have null
> >>
> >>
> >values.
> >
> >
> >>But, the existing data does contain nulls.
> >>
> >>So, within the same table, I need to swap the values of 4 columns with
the
> >>values of 4 other columns, unless the 4 other columns contain null
values.
> >>When they do, they need to simply be filled with the values of the first
4
> >>columns.
> >>
> >>CREATE TABLE d (id int, type tinyint, year int, nmct varchar (3), cid
int
> >>NULL, ctype tinyint NULL, cyear int NULL, cnmct varchar (3) NULL)
> >>
> >>INSERT INTO d VALUES
> >>(123456789, 15, 199612, 'ABC', 234567890, 20, 199712, 'DEF')
> >>INSERT INTO d VALUES
> >>(345678901, 10, 200012, 'GHI', NULL, NULL, NULL, NULL)
> >>
> >>So, I need:
> >>
> >>id type year nmct cid
> >>ctype cyear cnmct
> >>123456789 15 199612 ABC 234567890 20
> >>
> >>
> >199712
> >
> >
> >>DEF
> >>345678901 10 200012 GHI NULL NULL
NULL
> >>NULL
> >>
> >>to become:
> >>
> >>234567890 20 199712 DEF 123456789 15 199612
> >>ABC
> >>345678901 10 200012 GHI 345678901 10 200012
> >>GHI
> >>
> >>I can do the swap when all the fields have values. But, haven't figured
> >>
> >>
> >out
> >
> >
> >>how to handle the nulls. I have researched SET...CASE...WHEN. But
> >>everything I've found updates only a single column. Any suggestions?
> >>
> >>In the existing data, if one of the columns has a null value, all 4 of
the
> >>columns will have null values. In other words, cid can't be null and
> >>
> >>
> >ctype
> >
> >
> >>contain a value. However, I anticipate future situations where any one
of
> >>the fields may contain null values. So, if you can advise how to handle
> >>
> >>
> >it
> >
> >
> >>in such a case, that would be great.
> >>
> >>Thanks!
> >>
> >>Iris
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>