Update with Inner Join

Question

Update with Inner Join

Viewing 11 posts - 16 through 25 (of 25 total)

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

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

martin.kerr 34088 (12/5/2011)
but even with the joining table de-duped it still produces the same count. Does it matter if the original table i am trying to update to NULL has dupes? i.e. 10000 cities which are Hong Kong

No, it's completely irrelevant (to the UPDATE) if the update target table has 'dupes'. In this very specific case, your update might look like this:

UPDATE target

SET city = NULL

WHERE city = 'Hong Kong'

^{“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

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 2

yes thats correct but i want to do multiple ones at the same time, i.e. hong kong, singapore, monaco. these are all stored in the joining table but still not updating correctly. There are nearly 100k records that match my criteria but it is only updating 25k and there are no duplicates in my joining table

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

Have you checked the target table after the update to see if any values remain unchanged?

^{“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

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 4

yes i have and there are 75k that have not been updated

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

martin.kerr 34088 (12/5/2011)
yes i have and there are 75k that have not been updated

If you run the original SELECT...

select *

FROM MOHG_Processing_TestData.dbo.Stage2test

INNER JOIN MOHG_Processing_TestData.dbo.Country_Transform

ON MOHG_Processing_TestData.dbo.Stage2test.Address1_City = MOHG_Processing_TestData.dbo.Country_Transform.DQCorrection COLLATE Latin1_General_CI_AI

-- 98,797

...it returns 98,797 rows. You then run the UPDATE FROM ...

UPDATE [MOHG_Processing_TestData].[dbo].[Stage2test]

SET Address1_City = NULL

FROM MOHG_Processing_TestData.dbo.Stage2test

INNER JOIN MOHG_Processing_TestData.dbo.Country_Transform

ON MOHG_Processing_TestData.dbo.Stage2test.Address1_City = MOHG_Processing_TestData.dbo.Country_Transform.DQCorrection COLLATE Latin1_General_CI_AI

-- 25,784

which updates 25,784 rows, then you run the original SELECT again...

select *

FROM MOHG_Processing_TestData.dbo.Stage2test

INNER JOIN MOHG_Processing_TestData.dbo.Country_Transform

ON MOHG_Processing_TestData.dbo.Stage2test.Address1_City = MOHG_Processing_TestData.dbo.Country_Transform.DQCorrection COLLATE Latin1_General_CI_AI

.. and it returns 75k rows?

^{“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

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 6

martin.kerr 34088

SSCommitted

Points: 1752

December 5, 2011 at 7:13 am

#1416671

yes that is correct

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 7

Hi Friend

The count for the select is 98797 and the count for update is 25784

as for the your above statement I show how you might be getting more records when using joins and less records when updating.

Table1 (1,2,3,4,5) -- table1 have 5records

Table2 (1,1,2,3,3,4,5,5) -- table2 have 8records

now when you use

Table1 inner join Table2

every matching row of table1 will compare it with each row of second table2 there for table1 (1) will be take twice in out put list as table2 have (1,1)

This is why you get multiple records and count of this join gets more

and when you update it to table1 which actually have 5 records thus it update only 5 records

there you getting total count as 8 and update count is 5

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 8

so are you saying that the nearly 100k record count is not a true version of the data? it is replicating some of the records?

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

How many rows are in the update target table [MOHG_Processing_TestData].[dbo].[Stage2test]?

^{“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

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 10

257,474 rows in target table, but i may have over looked; that because the criteria i was selecting on was duplicated maybe 4 times in the joining table that in my 100k results, it selected and showed maybe 1 record 4 times! therefore in the updatre it was only selecting the correct records, so i think all is good now, thanks for your help for all of today! 🙂

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

No worries Martin.

Here's a wee hint - before you write a query, find out as much useful information as you can from the tables which will be referenced by it. At an absolute minimum, that would be rowcounts. Next would be the distribution of values in the key columns - those used in joins and the 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