Link to home
Create AccountLog in
Avatar of JosephEricDavis

asked on

Oracle help on a query

I've got the following two relevant tables with relevant columns show...



Each record in the reservation table can potentially have a value for RequestedAssetTypeID and ReservedAssetTypeID which maps over to the AssetType.AssetTypeID column.

In the AssetType table we should have data that has unique TypeCodes.  However, somehow there was some invalid data imported into the table that has caused records with duplicate typecodes but different departmentIDs.  There should only be data from one department in the table, department 18.  Yet we have records sprinkled in from department 14 and department 43.

Normally I would just delete all records from the AssetType table where DepartmentID <> 18.  But because this is a live system I'm working in and some users have already made selections on the invalid data, such a delete would cause referential integrity issues with the Reservation table.

What I need is an update query that will replace the RequestedAssetTypeID and the ReservedAssetTypeID when these records refer to AssetTypeIDs of AssetTypes with a DepartmentID <> 18.  The AssetTypeIDs should be replaced with the AssetTypeID of the record that has the same TypeCode but with a DepartmentID = 18.

Hope this makes sense.
Avatar of tweaver2

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of awking00
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of JosephEricDavis


For sure... let me show you what I'm working with so far...

I've got the following query...

SELECT r.ReservationID,
cat.AssetTypeID, cat.TypeCode, cat.DepartmentID, cat.DisplayName,
nat.AssetTypeID, nat.TypeCode, nat.DepartmentID, nat.DisplayName
FROM Reservation r
JOIN AssetType cat ON r.ReservedAssetTypeID = cat.AssetTypeID
JOIN AssetType nat ON cat.TypeCode = nat.TypeCode
WHERE cat.DepartmentID <> 18 AND nat.DepartmentID = 18 AND r.Active = 1
AND r.ReservedPickupDate > SYSDATE;
--cat = current AssetType
--nat = new AssetType

And it returns data like this... (attached text file)

The query returns 69 records currently.  And as it turns out, the RequestedAssetTypeID and the ReservedAssetTypeID should both contain the same values for all of these records.  So I need both of these columns in the Reservation Table which currently contain the value cat.AssetTypeID to be replaced with the value nat.AssetTypeID.

Let me know if any more info is needed.