Aparanjith
asked on
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_A
[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
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_A
[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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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)
AND LTRIM(RTRIM(a.Prov_Type)) = LTRIM(RTRIM(b.Prov_Type ))
Please report the results of this query and we'll go from there.
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)
AND LTRIM(RTRIM(a.Prov_Type)) = LTRIM(RTRIM(b.Prov_Type ))
Please report the results of this query and we'll go from there.
ASKER
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.
Is the SELECT query posted by Dulton in #39989938 giving any data?
ASKER
No sharath, its not giving any data. Little confused why its not updating any data
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.
>> 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?
That means, there is no matching data between two tables. How do you want to join them?
ASKER
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?
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.
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.
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?