From what I have been reading it is possible to copy data from one
field into another by doing something similar to this:

UPDATE table
SET field2 = field1;

what I am looking to do is take the the information from several
fields and create one new field

ie: all of the info from field1 + first character of field2 + first
character of field3 + first charcter of field4 is used to create the
information in field5 for this I could do something like:

UPDATE table
SET field5 = CONCAT(field1, ',' ,field2, ',' ,field3, ',' ,field4);

but how do i get it to only use the first character of fields 2,3 and
4?

canajien wrote:
> From what I have been reading it is possible to copy data from one
> field into another by doing something similar to this:
> 
> UPDATE table
> SET field2 = field1;
> 
> what I am looking to do is take the the information from several
> fields and create one new field
> 
> ie: all of the info from field1 + first character of field2 + first
> character of field3 + first charcter of field4 is used to create the
> information in field5 for this I could do something like:
> 
> UPDATE table
> SET field5 = CONCAT(field1, ',' ,field2, ',' ,field3, ',' ,field4);
> 
> but how do i get it to only use the first character of fields 2,3 and
> 4?

You could use substr:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

-- 

  //Aho

On 2010-08-05 19:18, canajien wrote:
> From what I have been reading it is possible to copy data from one
> field into another by doing something similar to this:
> 
> UPDATE table
> SET field2 = field1;
> 
> what I am looking to do is take the the information from several
> fields and create one new field
> 
> ie: all of the info from field1 + first character of field2 + first
> character of field3 + first charcter of field4 is used to create the
> information in field5 for this I could do something like:
> 
> UPDATE table
> SET field5 = CONCAT(field1, ',' ,field2, ',' ,field3, ',' ,field4);
> 
> but how do i get it to only use the first character of fields 2,3 and
> 4?

Why would you like to do such thing?


/Lennart

On Aug 5, 1:24=A0pm, "J.O. Aho" <u...@example.net> wrote:
> canajien wrote:
> > From what I have been reading it is possible to copy data from one
> > field into another by doing something similar to this:
>
> > UPDATE table
> > SET field2 =3D field1;
>
> > what I am looking to do is take the the information from several
> > fields and create one new field
>
> > ie: all of the info from field1 + first character of field2 + first
> > character of field3 + first charcter of field4 is used to create the
> > information in field5 for this I could do something like:
>
> > UPDATE table
> > SET field5 =3D CONCAT(field1, ',' ,field2, ',' ,field3, ',' ,field4);
>
> > but how do i get it to only use the first character of fields 2,3 and
> > 4?
>
> You could use substr:http://dev.mysql.com/doc/refman/5.0/en/string-functi=
ons.html#function...
>
> --
>
> =A0 //Aho

thanks, I used:

UPDATE table SET field5 =3D CONCAT(field1, substring( field2, 1, 1 ) ,
substring( field3, 1, 1 ) , substring( field4, 1, 1 )  ) ;

and got what I needed

canajien wrote:
> From what I have been reading it is possible to copy data from one
> field into another by doing something similar to this:
> 
> UPDATE table
> SET field2 = field1;
> 
> what I am looking to do is take the the information from several
> fields and create one new field
> 
> ie: all of the info from field1 + first character of field2 + first
> character of field3 + first charcter of field4 is used to create the
> information in field5 for this I could do something like:
> 
> UPDATE table
> SET field5 = CONCAT(field1, ',' ,field2, ',' ,field3, ',' ,field4);
> 
> but how do i get it to only use the first character of fields 2,3 and
> 4?

You really don't want to do it that way because you would be duplicating 
data, thus introducing the possibility of anomalies. What if the 
underlying data in fields 1-4 changed? You'd have to take extra 
precautions to update the other field any time that happened.

It is much better to leave the fields "as is" and generate the format 
you want in a view. That way, the underlying data can change, and the 
correlated data remains consistent.