[RESOLVED] [2005] Help with SQL Update Command

[tuber]

I want to update the Status of the NASE_Records table if the MemberNum in that table does not Exist in the SAS_Download table.

I don't think my sub query WHERE clause is right but I can't really test it cause this is live data and I don't want to screw up my table.

UPDATE    NASE_Records
SET              Status = 'I'
WHERE     (NOT EXISTS
                          (SELECT     *
                            FROM          SAS_Download
                            WHERE      (SAS_Download.MemberNum = NASE_Records.MemberNum)))

Any help would be great, thanks!

[GaryMazzone]

Update NASE_Records Set
Status = 'I'
Where MemberNum Not In (Select MemberNum From SAS_Download)

[Tom Sawyer]

It should work, but isn't very efficient.

Try:

UPDATE    NASE_Records
SET              Status = 'I'
WHERE MemberNum NOT IN (SELECT MemberNum 
                            FROM          SAS_Download)

[tuber]

Oh, okay, yeah that does look alot better, thanks Gary, and Tom!!!!!!

[tuber]

Oh and thanks for the quick response!

[szlamany]

If this is MS SQL - then you can use this.

Update NASE_Records Set Status = 'I'
   From NASE_Records NS
   Left Join SAS_Download SD on SD.MemberNum=NS.MemberNum
   Where SD.MemberNum is null

This is truly efficient - as it's using a simple join.

And the even nicer thing about this type of UPDATE/FROM is that you can test it by changing it to a SELECT

Select *
   From NASE_Records NS
   Left Join SAS_Download SD on SD.MemberNum=NS.MemberNum
   Where SD.MemberNum is null

This will show you - in advance - what the population of rows is that will be updated.

[tuber]

This is more efficient huh because you're not looping through the whole table?

[Tom Sawyer]

It would depend on the index. If there's not one on the MemberNum column, then the whole table would need to be scanned to get the rows included in the join. If there's one on that column in both tables, then the indexes can just be compared and it's much quicker.

[szlamany]

This is more efficient huh because you're not looping through the whole table?

If member num is a PK on both tables the query-optimizer is going to see the simple-JOIN and the even simpler-WHERE/NULL and get those rows back as quickly as possible.

There is a good chance that the query-optimizer will discover that all three variations of this query are the same - but that's only going to happen in MS SQL, where that logic has 50 years of maturity.

I also prefer my version as it's easier to read - in my opinion

You can do a BEGIN TRAN/ROLLBACK around all three of these and look at the execution plan in Mgt Studio and report back to us exactly which one is better...

[szlamany]

...Like Tom said

[tuber]

It's not a primary key though, and we're not indexing this table, although we should because it's pretty massive.

The primary key is just a unique incremented int

I'm not very knowledgeable of SQL.

I'm using SQL 2003

[szlamany]

You should make it an ALTERNATE KEY - that's an easy process. Although INSERT's will take longer...

btw - SQL 2000 and SQL 2005 are versions - 2003 is not a version of MS SQL.

[Tom Sawyer]

Well, run it twice in Query Analyzer and it'll cache the query plan and be pretty quick regardless. The join would be much faster, though, as using NOT IN with a subquery is one of the most inefficient things you can do and I apologize for showing it to you.

It's just so damn easy to type in, though ...

[szlamany]

I wasn't expecting a report of the timing of the query - I was more interested on seeing the OP's actual execution plan info - like table scan vs index - how many rows were eaten up in each step. Some of that can be pretty influential on how the query will run.

[tuber]

Yeah I'm retarded, we're running SBS 2003 and SQL 2000

I have learned much today!

[tuber]

And I knew nothing of this SQL Query Analyzer until 4 minutes ago.

[szlamany]

The primary key is just a unique incremented int

Not like I want to harp on this point - and I realize this was not a thread about db design

...but with MS SQL there are some real benefits to not using a unique incremented int key. That's a common thing that ACCESS developers have gotten used to - but with MS SQL having a more natural key is sometimes better.

If MEMBERNUM is supposed to be unique anyway...

And you have lots of WHERE MEMBERNUM=xyz type queries...

Then MEMBERNUM should be the PK.

[tuber]

I completely agree, and I'm not sure why it was set up the way it was.

One other question:

Why does this not show up in the SQL Designer?

"The SQL Designer does not support the Optional FROM cla etc...

UPDATE    NASE_Records
SET              Status = 'I'
FROM         NASE_Records NS LEFT JOIN
                      SAS_Download SD ON SD.MemberNum = NS.MemberNum
WHERE     SD.MemberNum IS NULL

[szlamany]

I don't use SQL DESIGNER...

And now that you tell me it doesn't support UPDATE/FROM I won't!

UPDATE/FROM is definitely a unique kind of syntax - I'm not sure how many sql-engines allow it.

We use it extensively here because you can do use the JOIN'd table on the right side of the SET xyz= statements - so you can do some very complex updates without a series of sub-queries or building temp tables in advance.

[edit] I just googled a bit and it might be because the Designer is using ODBC ? [/edit]

[tuber]

Ah okay. Well I'm going to run this off a DTS package after the office closes here. So I hope it works. Thanks again for all the help and the many clarifications.