Update Script Help

Other SQL Server Topics
1

Hi All!

I am very green at SQL Server 2008 and trying to get some thoughts from those much more experienced then I on my basic script before I run it. I have two tables called ExpressDB.dbo.epic_LineItems (AKA: LineItems) and [ExpressDB].[dbo].[epic_LineItemCustomFields] (AKA CustomFields).

I need the LineItems SatusID field to be set to '51' if the Value field in CustomFields equals 'Disposed'. The LineItemID field is the common field between the two tables. Below is the code I want to run. Let me know what you guys think and if it will even work how I hope.

Thanks!
Wave

UPDATE ExpressDB.dbo.epic_LineItems
SET StatusID=
CASE
WHEN Value IS ‘Disposed’ THEN '51'
END
inner join [ExpressDB].[dbo].[epic_LineItemCustomFields] on [ExpressDB].[dbo].[epic_LineItems].LineItemId = [ExpressDB].[dbo].[epic_LineItemCustomFields].LineItemId

2

I would be inclined to do it like this:

UPDATE LI
SET StatusID = '51' 
FROM ExpressDB.dbo.epic_LineItems AS LI
    INNER JOIN [ExpressDB].[dbo].[epic_LineItemCustomFields] AS CF
        ON CF.LineItemId = LI.LineItemId
       AND CF.Value = ‘Disposed’
WHERE LI.StatusID IS NULL OR StatusID <> '51' -- Don't UPDATE if already set to 51 !!

Are you sure you want to NAME the database in the query? If it will only ever run on "current" database (rather than be run IN a different database and need to act ON the [ExpressDB] database, then better to leave that out - that way it you rename / copy [ExpressDB] to something else (such as a DEV DB or a TEST DB) then it will still work.

If this DOES have to operate on a database which is NOT the current DB then i recommend that you use a SYNONYM instead of the actual database name - that way IF it changed you just need to change the definition of the SYNONYM rather than every single reference to [ExpressDB] in the code. (Same thing if you have any 4-part naming for a table e.g. MyRemoteServer.MyRemtoeDB.dbo.MyRemtoeTable)

1 Like
3

Hmm you make some excellent points. I really had no idea you could call SYNONYM's. Since this will only ever run on ExpressDB then you are right I don't need to call it.

I noticed you called on LI in the UPDATE statement before setting it in the FROM statement. Will this be a problem?

Also i know <> means does not equal but how does that keep the nulls and 51 values from being updated?

Thanks!
Wave

4

I'd leave off the WHERE, as SQL will check for that anyway and adding the WHERE makes it somewhat less clear what is going on.

As to using the table alias LI in the UPDATE, that is a requirement to guarantee an accurate UPDATE when doing a JOIN, so you should always do that.

UPDATE LI
SET StatusID = '51' 
FROM dbo.epic_LineItems AS LI
INNER JOIN dbo.epic_LineItemCustomFields AS CF
    ON CF.LineItemId = LI.LineItemId
    AND CF.Value = ‘Disposed’
1 Like
5

So just to make sure I haven't missed something this will only update the StatusID field or the LineItemID when the LineItemID has a Value of 'disposed'. This won't change all the StatusID column of all LineItemID's if it finds just a single disposed? The absence of a WHEN IS is throwing me off and I want to make sure i'm not about to fill the whole column of StatusID with '51' because a single 'Disposed' was detected.

6

The " AND CF.Value = ‘Disposed' " means only matching LineItemIds with a Value of Disposed will be changed.

In fact, your original code would have set all the StatusIDs to NULL if a LineItemId match was found but the value was <> 'Disposed'. By restricting the JOIN itself, we limit the rows that get UPDATEd.

1 Like
7

OOOOHHHHHH. That makes a lot of sense now that you say it. I'm glad you said that or I never would of known how bad my code was without you mentioning it. Thanks for the help! I really appreciate the lesson!

Thanks Again,
Wave