Solved

update query of mine, not pulling any records.

Posted on 2014-04-09
11
306 Views
Last Modified: 2014-04-11
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
0
Comment
Question by:Aparanjith
  • 5
  • 4
  • 2
11 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39989871
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
 
LVL 6

Expert Comment

by:Dulton
ID: 39989899
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
 

Author Comment

by:Aparanjith
ID: 39989912
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
 
LVL 6

Expert Comment

by:Dulton
ID: 39989938
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
 

Author Comment

by:Aparanjith
ID: 39989950
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
Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

 
LVL 40

Expert Comment

by:Sharath
ID: 39990006
Is the SELECT query posted by Dulton in #39989938 giving any data?
0
 

Author Comment

by:Aparanjith
ID: 39990020
No sharath, its not giving any data. Little confused why its not updating any data
0
 
LVL 6

Expert Comment

by:Dulton
ID: 39990039
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39990040
>> 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
 

Author Comment

by:Aparanjith
ID: 39991520
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
 
LVL 6

Expert Comment

by:Dulton
ID: 39993955
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now