manipulate data - 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)

manipulate data

Author

Topic

ptran
Starting Member

4 Posts

Posted - 2004-06-21 : 21:55:15

hi all,

I'm having the following data, and wonder if sql be able to manipulate around. As you can see the company name is a row under, and I want to move these up to the correct row and delete it.

Company Address City state zip phone
NULL 2 E MAIN ST MARLTON, NJ 08053 (856) 596-9549
7-ELEVEN FOOD STORE NULL NULL NULL NULL
NULL 300 E GREENTREE RD # 300 MARLTON, NJ 08053 (856) 797-1112
7-ELEVEN FOOD STORE NULL NULL NULL NULL

--------------------------------
Company Address City state zip phone
7-ELEVEN FOOD STORE 2 E MAIN ST MARLTON, NJ 08053 (856) 596-9549
7-ELEVEN FOOD STORE 300 E GREENTREE RD # 300 MARLTON, NJ 08053 (856) 797-1112

thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-21 : 22:57:53

--Well, assuming that table has some kind of a numeric primary key. ????????
DECLARE @companydata TABLE(
companydata_key INT IDENTITY(1,1) PRIMARY KEY,
Company VARCHAR(55),
Address VARCHAR(55),
City VARCHAR(55),
State CHAR(2),
Zip CHAR(5),
Phone CHAR(14))

INSERT @companydata(
Company,
Address,
City,
State,
Zip,
Phone)

SELECT NULL, '2 E MAIN ST', 'MARLTON', 'NJ', '08053', '(856) 596-9549'
UNION ALL
SELECT '7-ELEVEN FOOD STORE', NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT NULL, '300 E GREENTREE RD # 300', 'MARLTON', 'NJ', '08053', '(856) 797-1112'
UNION ALL
SELECT '7-ELEVEN FOOD STORE', NULL, NULL, NULL, NULL, NULL

SELECT * FROM @companydata

UPDATE cd1
SET cd1.Company = cd2.Company
FROM
@companydata cd1
INNER JOIN @companydata cd2 ON cd1.companydata_key = (cd2.companydata_key - 1)
WHERE
cd1.Company IS NULL

SELECT * FROM @companydata

--Not sure about how specific you need to get on this.
DELETE @companydata
WHERE
Address IS NULL
AND City IS NULL
AND State IS NULL
AND Zip IS NULL
AND Phone IS NULL

SELECT * FROM @companydata

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources