manipulating data question. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

manipulating data question.

Author

Topic

mhr1971
Starting Member

1 Post

Posted - 2004-03-05 : 16:52:30

I have a table that has a Fantype field including other fields with 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.

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

----------------------------------------
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;
----------------------------------------

???????????????????????????????????????????????????

Need to replace the first value of 1 in a field and
replace it with a 2. (like 1023 would now be 2023)
????????????????????????????????????????????????????

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 19:02:23

[code]
--This is an example of your existing table and data
create table fans (fantype varchar(5), listprice int, someotherdata varchar(8))

insert into fans
select 'CA', 100, 'DATA1' union all
select 'BA', 100, 'DATA3' union all
select 'DA', 100, 'DATA4' union all
select 'CABD', 200, 'DATA2'

--before insert
select * from fans

--insert the new records
insert fans
select
case when fantype = 'CA' then 'TPD' when fantype = 'CABD' then 'TPB' end fantype,
listprice,
someotherdata
from fans
where fantype in ('CA','CABD')

--after insert
select * from fans

drop table fans
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources