Newbie: Copy Data between columns in one table

From: Edwin van der Voort <Edwin.vanderVoort_at_msb.nl>
Date: Fri, 05 Feb 1999 16:26:42 +0100
Message-ID: <36BB0DB2.602CB046@msb.nl>

I'm a SQL Newbie so maybe my problem is simple to solve;

I have a table with the name : table1
In this table I have different columns : handle, family, c0, c1,c2,c3,c5 etc.

SQLWKS> select handle, c0 from table1 where family = 66; HANDLE C0

---

---

     17203 B00001
     17204 B08959
     17205 B08966
     17206 BB0050

4 rows selected.

SQLWKS> select handle, c3 from table1 where family = 66; HANDLE C3

---

---

     17203 AF TE DRUKKEN GEGEVENS V02060
     17204 AF TE DRUKKEN GEGEVENS V02060
     17205 NETTO BESTAND BVGB OMGEREKEND
     17206 OMGEREKEND NETTO AF BESTAND B.V.G.B

4 rows selected.

select handle, c0 from table1 where family = 47;

12156 ICVM507.B00001

9871   ICVM507.B00001
9872   ICVM507.B08959
9873   ICVM507.B08966

16254 ACCP008.B00001 5 rows selected.

select handle, c5 from table1 where family = 47;

12156
9871
9872
9873
16254

I'm looking for a solution to move the data from the column c3 to the colomn c5 with the variable from column c0 as key for the select. So in fact I have put a simple SQL to do this here. But this is the manual way. I'm looking for a solution to update column c5 with the data of c3 with as selecting criteria : the value from the select. Here is the code for the manual way !

---

update table1
SET c5 = ( select c3 from table1 where family = 66 and c0 like 'B00001')
where c0 like '%B00001%'
/

12156 AF TE DRUKKEN GEGEVENS V02060
9871 AF TE DRUKKEN GEGEVENS V02060
9872
9873
16254 AF TE DRUKKEN GEGEVENS V02060

---

The code above is copy the data from c3 to c5 with as selecting key B00001.

The code i'm looking for should give the following result:

12156 AF TE DRUKKEN GEGEVENS V02060
9871 AF TE DRUKKEN GEGEVENS V02060
9872 NETTO BESTAND BVGB OMGEREKEND
9873 NETTO BESTAND BVGB OMGEREKEND
16254 AF TE DRUKKEN GEGEVENS V02060 Many thanks for the help

Edwin van der Voort
(software consultant MSB)

(email: edwin.vandervoort_at_msb.nl)
Received on Fri Feb 05 1999 - 09:26:42 CST