Insert Problem - 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

SQL Server Development (2000)

Insert Problem

Author

Topic

Phibian
Starting Member

32 Posts

Posted - 2002-12-02 : 15:18:46

I have a table Flight as follows:

ID ExceptionID Price FareClassID Active
1 0 30 1 1
2 0 20 2 1
3 1 40 1 1

Using the above data as an example, I am trying to get to the following:

ID ExceptionID Price FareClassID Active
1 0 30 1 0
2 0 20 2 0
3 1 40 1 0
4 0 30 1 1
5 0 20 2 1
6 4 40 1 1

Right now, I'm attempting to accomplish this by doing an update followed by an insert. The update sets the active column of active flights to 0, and makes the FareClassID column negative (to keep track of what flights were previously active).

Then I'm inserting my rows using a select statement to copy all rows with a negative FareClassID. My problem is that flights with an exception ID must be updated to refer to the new ID that has just been created.

IE ExceptionID for Flight ID 6 must be 4 instead of 1, since flight 1 has been deactivated.

I've tried a number of approaches, to no avail.

Any ideas?

baldeep
Starting Member

18 Posts

Posted - 2002-12-02 : 15:52:56

Probably what you'll want to do is create an "OldId" field that references the original records.

I think this script will do what you want.


DROP TABLE Flight
DROP TABLE #FlightWork

CREATE TABLE Flight (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	ExceptionID INT NULL,
	Price MONEY NULL,
	FareClassID INT NULL,
	Active BIT NULL)
GO

INSERT INTO Flight (ExceptionId, Price, FareClassID, Active) VALUES (0, 30, 1, 1)
INSERT INTO Flight (ExceptionId, Price, FareClassID, Active) VALUES (0, 20, 2, 1)
INSERT INTO Flight (ExceptionId, Price, FareClassID, Active) VALUES (1, 40, 1, 1)
GO

SELECT * INTO #FlightWork FROM Flight
GO

ALTER TABLE #FlightWork
ADD OldId INT NULL
GO

INSERT INTO #FlightWork (ExceptionId, Price, FareClassID, Active, OldId)
SELECT ExceptionId, Price, FareClassID, Active, ID
FROM Flight
GO

--This will set the ExceptionIds.  The ISNULL is to deal with the fact
--that you're using zeros instead of NULLs in the ExceptionID column.
UPDATE #FlightWork SET ExceptionId = ISNULL((SELECT ID FROM #FlightWork WHERE F.ExceptionId = OldId), 0)
FROM #FlightWork FW INNER JOIN Flight F ON FW.OldId = F.ID
GO

--I think #FlightWork contains the result you're looking for.

--Baldeep

Phibian
Starting Member

32 Posts

Posted - 2002-12-02 : 18:27:48

Thanks!

Based on your feedback I was able to come up with an approach that worked (not quite the same code, but very close).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources