Using one Update query to update multiplr values

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 2005 Forums

SSIS and Import/Export (2005)

Using one Update query to update multiplr values

Author

Topic

capper
Starting Member

1 Post

Posted - 2009-04-18 : 02:22:39

Hi guys

I have an SSIS package which is grabbing data from one database and importing it into another. I have a column which I need to change the values for depending on certain criteria.

There are 70 odd criteria I need to update on, and I REALLY do not want to do a derived column (using a replace statement) for each criteria.

I used an OLE DB Command object and used and update statement PER criteria, a snippet below;

 UPDATE    StudDetails
SET              HomeGroup = 'bTGB03'
WHERE     (HomeGroup = 'B03')

UPDATE    StudDetails
SET              HomeGroup = 'bTGB04'
WHERE     (HomeGroup = 'B04')

UPDATE    StudDetails
SET              HomeGroup = 'bTGB05'
WHERE     (HomeGroup = 'B05')

UPDATE    StudDetails
SET              HomeGroup = 'bTGB06'
WHERE     (HomeGroup = 'B06')

UPDATE    StudDetails
SET              HomeGroup = 'bTGB07'
WHERE     (HomeGroup = 'B07')

But when I debugged it never finished. The data in my table was all updated correctly but the object just stayed yellow and the debug would not go to the next task. I assume this is because of the huse update query. When I tried with only 2-3 criteria, it worked and the debug completed.

I have searched Google, but can't find too much help. I saw someone mention using CASE statements, but I can't get them to work. This is what I got:

UPDATE StudDetails
SET HomeGroup =
CASE
    WHEN HomeGroup = 'B02'
    THEN HomeGroup = 'bTGB02'
    WHEN HomeGroup = 'B01'
    THEN HomeGroup = 'bTGB01'
END
WHERE (HomeGroup = 'B02') OR (HomeGroup = 'B01')

When I try to run this, SQL complains about invalid syntax near the =. I have determined it to be the = next to the 'bTGB02' text, but I have tried different things without success.

I am not proficient in SQL queries (just started using them last week), but I need to get this sorted ASAP. Please help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:20:33

it should be


UPDATE StudDetails
SET HomeGroup =
CASE
    WHEN HomeGroup = 'B02'
    THEN 'bTGB02'
    WHEN HomeGroup = 'B01'
    THEN 'bTGB01'
END
WHERE (HomeGroup = 'B02') OR (HomeGroup = 'B01')

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-21 : 10:01:21

It looks like there is a pattern to your updates and perhaps you could use just one update statement. For instance:


Update StudDetails Set HomeGroup = Right(HomeGroup, 3)

If that is not the case, I would recommend creating a separate table with the old and new values and then using a single update statement. That way if you need to add values for your update, you don't have to touch your code.
For example:


Create Table UpdatePairs (OldVal varchar(50), NewVal varchar(50)

Insert Into UpdatePairs Select 'bTGB01', 'B01'
.
.
.
Insert Into UpdatePairs Select 'bTGB70', 'B70'


--In your SSIS Package:
Update StudDetails Set HomeGroup = NewVal
From StudDetails
Inner Join UpdatePairs On HomeGroup = OldVal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources