List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:January 12 2004 1:42pm
Subject:Re: metric conversions
View as plain text  
* Richard Davies
> I recieve measurements for a product sometimes in inches, sometimes in
> centimeters.
>
> These are stored in table1
> int not null primary key, measurement1 int, measurement2 int, type
> varchar(255)
>
> I need to extract data from this table and store it in another
> table with all measurements converted to inches.
>
> insert into table2 select from table1 no, measurement1+0.625,
> measurement2+0.063, 'Style';
>
> Would it be possible to have a field in table1 that indicates
> centimeters or inches and modify the insert statement to check
> this field and divide everything by 2.54 before adding and inserting?
> If this is possible how would I do it I can't quite make it work.

It's possible. Changing the table to include a 'inches-flag':

ALTER TABLE table1 ADD inches TINYINT NOT NULL;

This defaults to 0 (=false=centimeters). Set it to 1 to indicate that inches
is used.

Output as centimeters:

SELECT id,
    IF(inches,measurement1/2.54,measurement1) m1,
    IF(inches,measurement2/2.54,measurement2) m2
  FROM table1

Output as inches:

SELECT id,
    IF(inches,measurement1,measurement1*2.54) m1,
    IF(inches,measurement2,measurement2*2.54) m2
  FROM table1

--
Roger

Thread
metric conversionsRichard Davies12 Jan
  • Re: metric conversionsMartijn Tonies12 Jan
  • Re: metric conversionsRoger Baklund12 Jan
  • Re: metric conversionsDiana Soares12 Jan
  • Re: metric conversions SOLVEDRichard Davies12 Jan