Solved

update query of mine, not pulling any records.

Posted on 2014-04-09
11
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
LVL 41

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 41

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Data Warehouse / Data Marts 4 40
SQL Server how to use a VARIABLE to link tables in a SQL Script? 3 41
SQL Query 9 26
sql trace 4 24
A procedure for exporting installed hotfix details of remote computers using powershell
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
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.

737 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