UPDATE with CASE Statement not updating all Records

Question

UPDATE with CASE Statement not updating all Records

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Unfortunately I'm unable to duplicate the problem and it is just a simple UPDATE Statement.

The Data originated from an old AS400 Legacy System.

I was asked for help from the former CIO with a DTA error.= and I mentioned it to him and he told me that he ran into a similar issue and it had to do with the Data.

I will figure it out tomorrow.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I don't know what I was thinking on the CASE UPDATEs.:blush: dah.

I figured out how to solve the problem.

The first step is to INSERT records from another into a Table.

If I do a left JOIN with two other tables and use CASE Statements in an INSERT INTO ... SELECT I should get the intended results.

I will try in the morning, I have worked almost 50 hours this week already.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I'm trying to get all the records from the History Table (39,026) but after I join I get only 26,086.

What is wrong with my statement and how can I get all of the records in the History Table?

SELECT *

FROM HISTORY AS Hist

LEFT OUTER JOIN Account AS Act ON Act.AccountNumber= Hist.AccountNumber

LEFT OUTER JOIN Current AS Cur ON Cur.AccountNumber = Hist.AccountNumber

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Welsh Corgi (6/29/2012)
I'm trying to get all the records from the History Table (39,026) but after I join I get only 26,086.
What is wrong with my statement and how can I get all of the records in the History Table?
SELECT *
FROM HISTORY AS Hist
LEFT OUTER JOIN Account AS Act ON Act.AccountNumber= Hist.AccountNumber
LEFT OUTER JOIN Current AS Cur ON Cur.AccountNumber = Hist.AccountNumber

Show your WHERE clause.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I do not intend to use a WHERE Clause is ths scenario.

After I got the record COUNT correct I was going to incorporate an INSERT INTO with CASE Statments on the SELECT Statement.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Welsh Corgi (6/29/2012)
I do not intend to use a WHERE Clause is ths scenario.
After I got the record COUNT correct I was going to incorporate an INSERT INTO with CASE Statments on the SELECT Statement.
Thanks.

So in this query:

Welsh Corgi (6/29/2012)
I'm trying to get all the records from the History Table (39,026) but after I join I get only 26,086.
What is wrong with my statement and how can I get all of the records in the History Table?
SELECT *
FROM HISTORY AS Hist
LEFT OUTER JOIN Account AS Act ON Act.AccountNumber= Hist.AccountNumber
LEFT OUTER JOIN Current AS Cur ON Cur.AccountNumber = Hist.AccountNumber

There was no WHERE clause, and yet, your rowcount is less than if you simply comment out the two left joins?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

If I comment out the LEFT JOIN's I get 39,028 Records.

With the LEFT JOIN's uncommented I get 26,0876 records.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

The person who normally works with this data is going to complete the UPDATEs one at a time so I'm off the hook but a bit baffled.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Welsh Corgi (6/29/2012)
If I comment out the LEFT JOIN's I get 39,028 Records.
With the LEFT JOIN's uncommented I get 26,0876 records.

And that's the exact same query that you are running? No extra bits anywhere?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

It is a Simple Query. I just wanted to get the record counts right before I added a bunch of CASE Statements.

As soon as I add the first LEFT JOIN my record count drops.:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Welsh Corgi (6/29/2012)
It is a Simple Query. I just wanted to get the record counts right before I added a bunch of CASE Statements.
As soon as I add the first LEFT JOIN my record count drops.:unsure:

Is it exactly the same?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

It is exactly the same. Positively.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 13

Gazareth

One Orange Chip

Points: 27737

June 29, 2012 at 8:09 am

#1507140

Curiousier and Curiousier...

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

No need to reply to this thead, thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/