Update Query

Question

Update Query

b_boy

SSCertifiable

Points: 5145
More actions
November 3, 2008 at 6:06 am

#195409

Hello
When i run the following query:
SELECT Customers.CustomerID, Customers.Forename, cleaned_data.[first name], Customers.Surname, cleaned_data.surname AS Expr1,
Customers.Add1, UKBACK.[address 1],Customers.Postcode, cleaned_data.postcode AS BASPostcode, Customers.Add4, cleaned_data.town
FROM Customers INNER JOIN
cleaned_data ON Customers.CustomerID = cleaned_data.custno
WHERE (Customers.Add1 <> cleaned_data.[address 1]) AND (Customers.Postcode <> cleaned_data.postcode)
I get 256,000 results telling me that i have that 256000 records with different Address 1 and Postcode.
When i then update the table with the following:
UPDATE Customers
SET Postcode = cleaned_data.postcode,
Add1 = cleaned_data.[address 1],
Add4 = cleaned_data.town
FROM Customers RIGHT OUTER JOIN
cleaned_data ON Customers.CustomerID = cleaned_data.custno AND Customers.Add1 <> cleaned_data.[address 1] AND
Customers.Postcode != cleaned_data.postcode
Now that i have updated the customer table, and i run the first query, i still get the same result set of 256000 instead of 0!!
Can anyone kindly explain to me what am doing wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

First I'd be consistent with != and . Choose one.

Second, why the right join? Why not an inner join on the update? I suspect the problem is there, but I'm not positive. You might be updating from bad rows because of the right join,

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Even when i use <>, and an inner join it still returns the same thing.....which is strange.

winash-561361 SSC Enthusiast Points: 119 More actions · Answer 3

Are the datatypes and lengths of the cleaned_data and the Customers columns the same (just in case you've got truncation happening which is why you are still getting a mismatch on the join)?

Are there any triggers on the Customers table?

If you run this:

UPDATE Customers

SET Postcode = cleaned_data.postcode,

Add1 = cleaned_data.[address 1]

--,Add4 = cleaned_data.town

FROM Customers INNER JOIN cleaned_data ON Customers.CustomerID = cleaned_data.custno

And then run

SELECT Customers.postcode,cleaned_data.postcode,Customers.Add1,cleaned_data.[address 1] FROM Customers INNER JOIN cleaned_data ON Customers.CustomerID = cleaned_data.custno

do you still see different values for the columns?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Have you tried turning the UPDATE statement into a SELECT statement and see how many rows you get back?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?