Need to find first empty field, and update
I am working with a database schema that I cannot change - and I need to run a bulk update. I am moving data between source and target databases, with the same schema. I know this is awful, but it is what I am working with right now.
The schema is as follows:
Car
CarType1
CarType2
CarType3
CarType4
What I am trying to do - is bring over the CarTypes from my source database, by updating the target CarTypes.
My question is:
If my source record for car "BMW" has 3 CarTypes specified like so:
Car: BMW
CarType1: Fast
CarType2: Well Made
CarType3: Good Handling
CarType4: NULL
and my target has:
Car: BMW
CarType1: Fun Car
CarType2: NULL
CarType3: NULL
CarType4: NULL
How would I write an update 开发者_开发技巧statement such that CarType1-2-3 from the source would fill in the available NULL fields on the target? So Source:CarType1 -> Target:CarType2. And also - would there be a way to cancel out of the bulk update for this record and log a message if there wasn't room in the target for all of the source fields(say if there's 4 CarType values in my source, and only 3 NULL CarType columns in the corresponding target row?
Thank you!
I don't know about doing it in one statement but four UPDATE statements do the trick.
The final select shows you what columns have overflowed.
DECLARE @CarsSource TABLE (Car VARCHAR(32), CarType1 VARCHAR(32), CarType2 VARCHAR(32), CarType3 VARCHAR(32), CarType4 VARCHAR(32))
DECLARE @CarsDestination TABLE (Car VARCHAR(32), CarType1 VARCHAR(32), CarType2 VARCHAR(32), CarType3 VARCHAR(32), CarType4 VARCHAR(32))
INSERT INTO @CarsSource VALUES ('BMW', 'Fast', 'Well Made', 'Good Handling', NULL)
INSERT INTO @CarsSource VALUES ('Overflow', 'Fast', 'Well Made', 'Good Handling', 'Overflow')
INSERT INTO @CarsDestination VALUES ('BMW', 'Fun Car', NULL, NULL, NULL)
INSERT INTO @CarsDestination VALUES ('Overflow', 'Fun Car', NULL, NULL, NULL)
UPDATE @CarsDestination
SET CarType1 = s.CarType1
, CarType2 = s.CarType2
, CarType3 = s.CarType3
, CarType4 = s.CarType4
FROM @CarsDestination d
INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE d.Cartype1 IS NULL
UPDATE @CarsDestination
SET CarType2 = s.CarType1
, CarType3 = s.CarType2
, CarType4 = s.CarType3
FROM @CarsDestination d
INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE d.Cartype2 IS NULL
UPDATE @CarsDestination
SET CarType3 = s.CarType1
, CarType4 = s.CarType2
FROM @CarsDestination d
INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE d.Cartype3 IS NULL
UPDATE @CarsDestination
SET CarType4 = s.CarType1
FROM @CarsDestination d
INNER JOIN @CarsSource s ON s.Car = d.Car
WHERE d.Cartype4 IS NULL
SELECT *
FROM @CarsSource s
LEFT OUTER JOIN @CarsDestination d ON COALESCE(d.Cartype4, d.Cartype3, d.Cartype2, d.Cartype1) = COALESCE(s.Cartype4, s.Cartype3, s.Cartype2, s.Cartype1)
WHERE d.Car IS NULL
精彩评论