How to swap a set of 4 columns

[wickyd]

Hi folx

How do you swap a set of columns?

There are 4 columns, namely ADDRESS1, ADDRESS2, ADDRESS3 and ADDRESS4 which represent the postal address. DELIVER1, DELIVER2, DELIVER3 and DELIVER4 represent the delivery address.

How do I swap ADDRESS1 with DELIVER1, ADDRESS2 with DELIVER2, ADDRESS3 with DELIVER3 and ADDRESS4 with DELIVER4.

Thank you.

 

[Katy44]

What do you mean? Can you copy address1, put delivery1 in the original place and then put the copy of address 1 into delivery 1's place?

 

[Katy44]

Sorry, I didn't make that very clear - you would have to create the new columns, and drop the old ones.

So create column address1a
Put all of address1 data in there
Put delivery1 data into address1
Delete address1
rename

What database are you using?

 

[rrrkrishnan]

I think this is what you are looking for, based on my understanding of your question.

Update tbl_nm
Set address1=deliver1,
address2=deliver2,
address3=deliver3,
address4=deliver4,
deliver1=address1,
deliver2=address2,
deliver3=address3,
deliver4=address4

 

[JarlH]

You'll need a temporary column to store data while moving around:

ALTER TABLE tab1 ADD tempcol char(100)

UPDATE tab1 SET tempcol = address1;
UPDATE tab1 SET address1 = deliver1;
UPDATE tab1 SET deliver1 = tempcol;
and so on...

ALTER TABLE tab1 DROP tempcol;

 

[rrrkrishnan]

As far as I know, in any RDBMS this updt stmt should work, there is no need for a temp column if you are doing all the column updates in one single update stmt.
Update tbl_nm
Set address1=deliver1,
address2=deliver2,
address3=deliver3,
address4=deliver4,
deliver1=address1,
deliver2=address2,
deliver3=address3,
deliver4=address4

 

[JarlH]

rrrkrishnan,

You're absolutely right. I feel soooo stupid now...

 

[wickyd]

Hi

I had to use the following:

ALTER TABLE DEBTORS ADD TEMPCOL CHAR(100);

UPDATE DEBTORS SET TEMPCOL = ADDRESS1;
UPDATE DEBTORS SET ADDRESS1 = DELIVER1;
UPDATE DEBTORS SET DELIVER1 = TEMPCOL;

UPDATE DEBTORS SET TEMPCOL = ADDRESS2;
UPDATE DEBTORS SET ADDRESS2 = DELIVER2;
UPDATE DEBTORS SET DELIVER2 = TEMPCOL;

UPDATE DEBTORS SET TEMPCOL = ADDRESS3;
UPDATE DEBTORS SET ADDRESS3 = DELIVER3;
UPDATE DEBTORS SET DELIVER3 = TEMPCOL;

UPDATE DEBTORS SET TEMPCOL = ADDRESS4;
UPDATE DEBTORS SET ADDRESS4 = DELIVER4;
UPDATE DEBTORS SET DELIVER4 = TEMPCOL;

ALTER TABLE DEBTORS DROP TEMPCOL;

rrrkrishnan, unfortunately your solution didn't work with the version of DBISAM I am using.

Thanks for the help.