July 19, 2018 at 11:53 am
SELECT x.pkproductid
,x.supplierbarcodecorrect
,x.Code
,x.pksupplierid
from Entertainer.dbo.[29] x
left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID
and spk.fkSupplierID = x.pkSupplierID
The above query returns me 2000 rows.
I want to update the supplierbarcode column in the dbo.supplierpack table so that it matches the supplierbarcodecorrect column in the table Entertainer.dbo.29.
I tried the following update statement:
update spk
set spk.supplierbarcode = x.supplierbarcodecorrect
from Entertainer..[29] x
left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID
and spk.fkSupplierID = x.pkSupplierID
But it says 0 rows were updated.
How can I get all 2000 rows to update?
July 19, 2018 at 12:08 pm
That LEFT JOIN is potentially a problem. For the rows in supplierpack which have no match in Entertainer, are you hoping that supplierbarcode will be set to NULL? Can you try again with an INNER join?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 19, 2018 at 12:18 pm
Phil Parkin - Thursday, July 19, 2018 12:08 PMThat LEFT JOIN is potentially a problem. For the rows in supplierpack which have no match in Entertainer, are you hoping that supplierbarcode will be set to NULL? Can you try again with an INNER join?
It's more than potentially a problem. He's trying to update the table on the right side of the join, so if there is no match, then there are no records to update. BIG PROBLEM.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 19, 2018 at 12:25 pm
Shaykh - Thursday, July 19, 2018 11:53 AMSELECT x.pkproductid
,x.supplierbarcodecorrect
,x.Code
,x.pksupplieridfrom Entertainer.dbo.[29] x
left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID
and spk.fkSupplierID = x.pkSupplierIDThe above query returns me 2000 rows.
I want to update the supplierbarcode column in the dbo.supplierpack table so that it matches the supplierbarcodecorrect column in the table Entertainer.dbo.29.
I tried the following update statement:
update spk
set spk.supplierbarcode = x.supplierbarcodecorrect
from Entertainer..[29] x
left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID
and spk.fkSupplierID = x.pkSupplierIDBut it says 0 rows were updated.
How can I get all 2000 rows to update?
Does the following return what you expect?
SELECT
[sp].*
, [ca1].[supplierbarcodecorrect]
FROM
[dbo].[supplierpack] [sp]
CROSS APPLY (SELECT [x].[supplierbarcodecorrect]
FROM Enterainer.dbo.[29] [x]
WHERE [sp].[fkprodid] = [x].[pkProductID]
AND [sp].[fkSupplierID] = [x].[pkSupplierID]) [ca1](supplierbarcodecorrect);
GO
If so, in a test environment, try this:
WITH base AS (
SELECT
[sp].*
, [ca1].[supplierbarcodecorrect]
FROM
[dbo].[supplierpack] [sp]
CROSS APPLY (SELECT [x].[supplierbarcodecorrect]
FROM Enterainer.dbo.[29] [x]
WHERE [sp].[fkprodid] = [x].[pkProductID]
AND [sp].[fkSupplierID] = [x].[pkSupplierID]) [ca1](supplierbarcodecorrect)
)
UPDATE [base] SET
[supplierbarcode] = [supplierbarcodecorrect];
GO
July 20, 2018 at 4:39 am
Phil Parkin - Thursday, July 19, 2018 12:08 PMThat LEFT JOIN is potentially a problem. For the rows in supplierpack which have no match in Entertainer, are you hoping that supplierbarcode will be set to NULL? Can you try again with an INNER join?
No I don't want rows in supplierpack with no match in Entertainer to be set to NULL - so yes it makes more sense to use an inner join however when I use an inner join select statement instead of the left join, I get 0 records. This explains why using my update statement it says 0 rows updated.
July 20, 2018 at 4:39 am
Lynn Pettis - Thursday, July 19, 2018 12:25 PMShaykh - Thursday, July 19, 2018 11:53 AMSELECT x.pkproductid
,x.supplierbarcodecorrect
,x.Code
,x.pksupplieridfrom Entertainer.dbo.[29] x
left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID
and spk.fkSupplierID = x.pkSupplierIDThe above query returns me 2000 rows.
I want to update the supplierbarcode column in the dbo.supplierpack table so that it matches the supplierbarcodecorrect column in the table Entertainer.dbo.29.
I tried the following update statement:
update spk
set spk.supplierbarcode = x.supplierbarcodecorrect
from Entertainer..[29] x
left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID
and spk.fkSupplierID = x.pkSupplierIDBut it says 0 rows were updated.
How can I get all 2000 rows to update?
Does the following return what you expect?
SELECT
[sp].*
, [ca1].[supplierbarcodecorrect]
FROM
[dbo].[supplierpack] [sp]
CROSS APPLY (SELECT [x].[supplierbarcodecorrect]
FROM Enterainer.dbo.[29] [x]
WHERE [sp].[fkprodid] = [x].[pkProductID]
AND [sp].[fkSupplierID] = [x].[pkSupplierID]) [ca1](supplierbarcodecorrect);
GOIf so, in a test environment, try this:
WITH base AS (
SELECT
[sp].*
, [ca1].[supplierbarcodecorrect]
FROM
[dbo].[supplierpack] [sp]
CROSS APPLY (SELECT [x].[supplierbarcodecorrect]
FROM Enterainer.dbo.[29] [x]
WHERE [sp].[fkprodid] = [x].[pkProductID]
AND [sp].[fkSupplierID] = [x].[pkSupplierID]) [ca1](supplierbarcodecorrect)
)
UPDATE [base] SET
[supplierbarcode] = [supplierbarcodecorrect];
GO
Unfortunately, the above select statement returned 0 records.
July 20, 2018 at 5:10 am
Shaykh - Friday, July 20, 2018 4:39 AMPhil Parkin - Thursday, July 19, 2018 12:08 PMThat LEFT JOIN is potentially a problem. For the rows in supplierpack which have no match in Entertainer, are you hoping that supplierbarcode will be set to NULL? Can you try again with an INNER join?No I don't want rows in supplierpack with no match in Entertainer to be set to NULL - so yes it makes more sense to use an inner join however when I use an inner join select statement instead of the left join, I get 0 records. This explains why using my update statement it says 0 rows updated.
This suggests that you are joining on the wrong column.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 20, 2018 at 7:08 am
Phil Parkin - Friday, July 20, 2018 5:10 AMShaykh - Friday, July 20, 2018 4:39 AMPhil Parkin - Thursday, July 19, 2018 12:08 PMThat LEFT JOIN is potentially a problem. For the rows in supplierpack which have no match in Entertainer, are you hoping that supplierbarcode will be set to NULL? Can you try again with an INNER join?No I don't want rows in supplierpack with no match in Entertainer to be set to NULL - so yes it makes more sense to use an inner join however when I use an inner join select statement instead of the left join, I get 0 records. This explains why using my update statement it says 0 rows updated.
This suggests that you are joining on the wrong column.
Or that you need to do an INSERT of missing records rather than an UPDATE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 22, 2018 at 4:25 pm
I was able to recreate this issue when one or both of the join criteria were NULL.
Quick Solution: Using the original code posted, add ISNULL(FieldName, 1) to all four fields in the FROM clause.
All 2,000 records should now update.
This is an instance of a SELECT and an UPDATE statement running against the same FROM clause producing different results. The reason for this is the addition of a FILTER operator to the query plan when it is an UPDATE which filters for "IS NOT NULL" on the join criteria. I believe this will be present in all UPDATE statements where the one or more of the fields being joined on are nullable.
It almost goes without saying the solution above would be better handled by a database change whereby neither of the join columns are nullable (or pretty much anything other than functions in your FROM clause!).
July 23, 2018 at 7:20 am
emcardle01 - Sunday, July 22, 2018 4:25 PMI was able to recreate this issue when one or both of the join criteria were NULL.Quick Solution: Using the original code posted, add ISNULL(FieldName, 1) to all four fields in the FROM clause.
All 2,000 records should now update.This is an instance of a SELECT and an UPDATE statement running against the same FROM clause producing different results. The reason for this is the addition of a FILTER operator to the query plan when it is an UPDATE which filters for "IS NOT NULL" on the join criteria. I believe this will be present in all UPDATE statements where the one or more of the fields being joined on are nullable.
It almost goes without saying the solution above would be better handled by a database change whereby neither of the join columns are nullable (or pretty much anything other than functions in your FROM clause!).
I don't think you've actually replicated the issue. I think the issue is that he's trying to update the right side of a left outer join where there are no matches. In his SELECT query he is only outputting fields from the left side of the join, so he can't tell that the right side is all nulls, because there are no matches. When he tries to update the right side, he gets zero results, because there are no matches to update.
Furthermore, you really do not want to use functions on fields in your JOIN or WHERE criteria, because it makes the criteria non-SARGable.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2018 at 10:23 am
Sample data would help. But without knowing more about "the WHY", it's hard to offer any more assistance. Either the intent is not what the OP said, or the relationship is improperly defined. =)
-Nate the DBA natethedba.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply