* 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
