June 11, 2014 at 1:25 am
I have this script in my database, but it always gives 2054 rows back and if I actually DO change something it doesn't even notice...
what am I doing wrong in here
UPDATE a
SET a.[omschrijving]=SP.[omschrijving]
,a.[verkoopprijs]=SP.[verkoopprijs]
,a.[gewijzigd]=getDate()
FROM [artikelen] a
LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE ((A.omschrijving != SP.[omschrijving]) OR (A.[verkoopprijs] != SP.[verkoopprijs]))
June 11, 2014 at 1:54 am
pk2dpvp (6/11/2014)
I have this script in my database, but it always gives 2054 rows back and if I actually DO change something it doesn't even notice...what am I doing wrong in here
UPDATE a
SET a.[omschrijving]=SP.[omschrijving]
,a.[verkoopprijs]=SP.[verkoopprijs]
,a.[gewijzigd]=getDate()
FROM [artikelen] a
LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE ((A.omschrijving != SP.[omschrijving]) OR (A.[verkoopprijs] != SP.[verkoopprijs]))
-- Firstly, your WHERE clause turns the outer join into an inner join:
UPDATE a SET
omschrijving = sp.omschrijving,
verkoopprijs = sp.verkoopprijs,
gewijzigd = getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE a.omschrijving <> sp.omschrijving
OR a.verkoopprijs <> sp.verkoopprijs
-- is this what you want - or do you want non-matches between the two tables to result in NULL
-- for artikelen.omschrijving and artikelen.verkoopprijs?
-- Secondly, test what the update is going to do before you run it, with a simple change:
SELECT
a.omschrijving, sp.omschrijving,
a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE A.omschrijving <> sp.omschrijving
OR A.verkoopprijs <> sp.verkoopprijs
-- and test again afterwards to ensure that the update has done what you want it to do.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:03 am
I'm still getting (2054 row(s) affected)
That should change to 0 after running once, I have no idea why it doesn't just do that
but whenever I change omschrijving or verkoopprijs it still doesn't notice it
(I change the value's in the SparePartsUpdate table since that counts as "Dummy table" after ssis import)
I have no idea how to fix this :p
All it has to do is see if anything has changed between sp and a to update the sp data to the a table data
June 11, 2014 at 2:10 am
What are the datatypes of those two columns in both tables?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:13 am
omschrijving: nvarchar
verkoopprijs: float
they mean:
omschrijving - Description
Verkoopprijs: - Price.
June 11, 2014 at 2:16 am
pk2dpvp (6/11/2014)
omschrijving: nvarcharverkoopprijs: float
they mean:
omschrijving - Description
Verkoopprijs: - Price.
Check that the datatypes are the same in both tables.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:17 am
they are the same in both tables
June 11, 2014 at 2:22 am
pk2dpvp (6/11/2014)
they are the same in both tables
Have you run the query I posted, labelled "test what the update is going to do before you run it"?
What does it return?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:24 am
It returns the 2054 rows that it is going to change...
...which never happens for some reason
I really just don't get it anymore;p
brains can't handle.
June 11, 2014 at 2:31 am
pk2dpvp (6/11/2014)
It returns the 2054 rows that it is going to change......which never happens for some reason
I really just don't get it anymore;p
brains can't handle.
Check the two float values in the output of this query, by eye.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:36 am
what should I check?;p
June 11, 2014 at 2:43 am
pk2dpvp (6/11/2014)
what should I check?;p
The two float values. Check if they are the same.
Run this query:
-- Secondly, test what the update is going to do before you run it, with a simple change:
SELECT
a.omschrijving, sp.omschrijving,
a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE A.omschrijving <> sp.omschrijving
OR A.verkoopprijs <> sp.verkoopprijs
Examine a.verkoopprijs and sp.verkoopprijs for a few rows of the output. Look for any differences.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:44 am
there are loads of differences, about 1000 of the 2054 have differences in the price
June 11, 2014 at 2:46 am
pk2dpvp (6/11/2014)
there are loads of differences, about 1000 of the 2054 have differences in the price
Can you show a few? Please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 2:49 am
a.price sp.price
11,53 11,53
5,7 5,7
1,67 1,67
100,57 132
13,1 11,97
0 9,81
20,8 20,8
9,93 9 ,93
10,74 10,74
6,98 6,98
3,34 3,34
14,47 14,47
12,08 12,08
23 23
14 14
7,7 7,7
1,281,28
4,434,44
18,7618,76
14,6614,66
1414
9,729,72
015,12
17,526,5
6,916,91
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply