update query of mine, not pulling any records.

Hi, all I am using an Update query which should update the total columns of the table based on the spread sheet values given by where condition. i have pulled that spread sheet results to a table and joined the these two tables in my update query, but it is not updating any records, its always showing 0 records affected. This is my update query, can anyone help me in this. I am attaching the sample data sheets of both update1 and update2 tables. Please suggest me something on this.

Query:

Update dbo.Update1

Set [SubSystemName] = b.[SubSystemName],
[PROV_TAX_ID] = b.[PROV_TAX_ID],
[Updated Bucket] = b.[Updated Bucket],
[Prov_Type] = b.[Prov_Type],
[Act_SQS_Amt]=b.[Act_SQS_Amt],
[Deal_Name]= b.[Deal_Name],
[TINs Provided by:] = b.[TINs Provided by:],
[Other name] = b.[Other name],
[Objector?] = b.[Objector?],
[IPA_Ind]= b.[IPA_Ind],
[Phys_Hosp_Ind]= b.[Phys_Hosp_Ind],
[Settlement_ID] = b.[Settlement_ID],
[Minimum Objection Date]= b.[Minimum Objection Date],
[Comments]=b.[Comments],
[Date added]= b.[Date added]
From dbo.Update1 a
Join dbo.Update2 b
on a.PROV_TAX_ID = b.Prov_Tax_ID
where a.PROV_TAX_ID = b.Prov_Tax_ID
AND   a.Prov_Type = b.Prov_Type
Update1.xlsx
Update2.xlsx
AparanjithAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DultonConnect With a Mentor Commented:
it's possible you have some blank spaces either in your source excel file or target db table. I wrapped each element in the where condition in LTRIM(RTRIM()) which will eliminate any leading or trailing blanks.


Update a
Set a.[SubSystemName] = b.[SubSystemName],
a.[PROV_TAX_ID] = b.[PROV_TAX_ID],
a.[Updated Bucket] = b.[Updated Bucket],
a.[Prov_Type] = b.[Prov_Type],
a.[Act_SQS_Amt]=b.[Act_SQS_Amt],
a.[Deal_Name]= b.[Deal_Name],
a.[TINs Provided by:] = b.[TINs Provided by:],
a.[Other name] = b.[Other name],
a.[Objector?] = b.[Objector?],
a.[IPA_Ind]= b.[IPA_Ind],
a.[Phys_Hosp_Ind]= b.[Phys_Hosp_Ind],
a.[Settlement_ID] = b.[Settlement_ID],
a.[Minimum Objection Date]= b.[Minimum Objection Date],
a.[Comments]=b.[Comments],
a.[Date added]= b.[Date added]
From dbo.Update1 a
Inner Join dbo.Update2 b
on LTRIM(RTRIM(a.PROV_TAX_ID)) = LTRIM(RTRIM(b.Prov_Tax_ID))
AND   LTRIM(RTRIM(a.Prov_Type)) = LTRIM(RTRIM(b.Prov_Type ))

Open in new window

--edit. eliminated redundant line in where condition
0
 
DultonCommented:
After seeing both of your upload files... the reason they won't update is because of the join. there are no records that match PROV_TAX_ID in the 2 sample excel sheets you uploaded. since there is no match over the join, nothing is performed. in this case, no updates....

Can you please explain in a little more detail what your data flow looks like and the purpose of Update 1 and Update 2, including their relationship to one another?
0
 
AparanjithAuthor Commented:
Hey Dulton, I have used the same query what you have sent to me. But it is not affecting any records, still saying 0 rows affected. As I have mentioned in two excels the sample data of each table. But it is not affecting the rows by this update query. Don't know why?
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
DultonCommented:
no update occurs because you're joining tables a and b over a couple of fields. Because there are no matches on those fields, no update occurs.

we can see the exact same thing with a select.

We'll use the from and where clauses of the query above and simply replace the update with a select. That way you'll see what rows are affected without altering any data:

SELECT *
From dbo.Update1 a
Inner Join dbo.Update2 b
on LTRIM(RTRIM(a.PROV_TAX_ID)) = LTRIM(RTRIM(b.Prov_Tax_ID))
AND   LTRIM(RTRIM(a.Prov_Type)) = LTRIM(RTRIM(b.Prov_Type ))

Please report the results of this query and we'll go from there.
0
 
AparanjithAuthor Commented:
Its a simple update dulton, I am trying to write an update query which looks for the spreadsheet table and updates the data. The update1 sheet which I have sent does not have all the records, i have about 112567 records.
0
 
SharathData EngineerCommented:
Is the SELECT query posted by Dulton in #39989938 giving any data?
0
 
AparanjithAuthor Commented:
No sharath, its not giving any data. Little confused why its not updating any data
0
 
DultonCommented:
It's because the same values don't appear in both lists for both Prov_Tax_Id and Prov_Type. By placing the join on tables update1 and update2, that's what you're requiring... if they did exist, the select query in #39989938  would have returned results,,,, and your update would update rows.
0
 
SharathData EngineerCommented:
>> No sharath, its not giving any data. Little confused why its not updating any data
That means, there is no matching data between two tables. How do you want to join them?
0
 
AparanjithAuthor Commented:
Hey guys, one quick question? As I am joining the spreadsheet table which is update2 and my table which need to be updated say update1. Do i need to have all the columns of update1 in update2 to get affected or if I have some columns of update1, then also will it affect?
0
 
DultonCommented:
If I understand the question correctly, you're asking if all columns in Update 1 need to be in Update 2 in order for the update to fire.... the answer in short is no. But it kind of depends on what all you're doing with it.

In update 2, you really only need the columns which you'll be interacting with... either in the where or the "set" part of the update.

example:
Update1 has fields
Field1
Field2
Field3
Field4
Field5
Field6

the update statement is this:
Update u1
Set u1.Field3 = u2.Field3
   ,u1.Field5 = u2.Field5
FROM Update1 AS u1
INNER JOIN Update2 AS u2
ON u1.Field1 = u2.Field1
AND u1.Field2 = u2.Field2


now, to perform the above statement given the structure of Update1, Update2 would only have to have for the Where clause: (Field1 & Field2), and for the Set clause: (Field3 & Field5).

This means that despite Update1 having fields: (Field4 & Field6), they aren't needed in Update2 given this very specific update statement requirement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.