Need best possible solution.

[mhr1971]

I have a table that has a Fantype field in it and other fields and data and two of the Fantypes are
CA
CABD
I need to add two new fans TPD & TPB these fan types are the same as
CA and data = TPD and same data
CABD and data = TPB and same data
I want to add the new fans but use the same data that is associated with the CA and CABD
I need to have in one table the CA CABD TPD TPB in one table and have the CA & TPD, CABD & TPB have the same data.

Am I going about this wrong.


????????????????????????????????????????
Not sure how to get this part done
I need to CREATE TABLE TEMP AND IMPORT
STRUCTURE FROM TABLE1 INTO TABLE TEMP.
????????????????????????????????????????

----------------------------------------
SELECT * INTO TEMP FROM TABLE1

DELETE FROM TEMP
WHERE FanType <> 'CA'
AND FanType <>'CABD';

UPDATE TEMP SET FanType = 'TPD'
WHERE FanType = 'CA';

UPDATE TEMP SET FanType = 'TPB'
WHERE FanType = 'CABD';
------------------------------------------

????????????????????????????????????????????????
I'm not sure if this is correct
SELECT * INTO TABLE1 FROM (SELECT * FROM TABLE1
UNION
SELECT * FROM TEMP);
????????????????????????????????????????????????


--------------------------------------
UPDATE TABLE1
SET LISTPRICE = LISTPRICE * .92;
----------------------------------------


???????????????????????????????????????????????????
I can remeber how to do this part either.
UPDATE TABLE1
Need to replace the first value in a field and replace it with a 2. (like 1023 would now be 2023)
????????????????????????????????????????????????????

 

[rrrkrishnan]

Say the table name is fan and the columns are col1, sol2, col3, fan_type. Now to add these two new fan types,
you can do

insert into fan(col1, col2, col3, fan_type)
select col1, col2, col3,
case when fan_type='CA' then 'TPD'
when fan_type='CABD' then 'TPB'
end
from fan where fan_type in ('CA','CABD');

 

[mhr1971]

Hi rrrkrishnan the sample code you posted did not work.

insert into FanSize1 (%To many field to list on here%)
select %To many field to list on here%
case when fantype='CA' then 'TPD'
when fantype='CABD' then 'TPB'
end
from FanSize1 where fantype in ('CA','CABD');

Say Invalid SQL statement. Is something missing like a comma or is this just not the correct structure.

Thanks,
Mark

 

[rrrkrishnan]

The syntax might not be exactly right depending on the RDBMS. Let me know what you are using. For ease in debugging, try to see if you can get the select stmt to work .
And then work with the insert stmt.
Sorry for not being of much help here.

 

[rrrkrishnan]

Also remeber you either specify the entire column list, or only the cols you are interested in inserting values for, do not use '*' - for all cols.