No worries Martin.

Here's a wee hint - before you write a query, find out as much useful information as you can from the tables which will be referenced by it....

How many rows are in the update target table [MOHG_Processing_TestData].[dbo].[Stage2test]?

The alphanumeric bit sounds interesting. Here's my take on generating a list of insecure PINs:

SELECT

x.PIN, y.InValidCode

FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)

CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))...

If you run the original SELECT...

select *

FROM MOHG_Processing_TestData.dbo.Stage2test

INNER JOIN MOHG_Processing_TestData.dbo.Country_Transform

ON MOHG_Processing_TestData.dbo.Stage2test.Address1_City = MOHG_Processing_TestData.dbo.Country_Transform.DQCorrection COLLATE...

Have you checked the target table after the update to see if any values remain unchanged?

okbangas (12/5/2011)

---

As being only 4 digits, any pin code is rather unsecure. If you want to eliminate "unsecure" pin codes, you'll eliminate quite a few pin codes, and thus making...

martin.kerr 34088 (12/5/2011)

---

but even with the joining table de-duped it still produces the same count. Does it matter if the original table i am trying to update to NULL has...

It will update just fine, but it's important for you to...

It doesn't matter if the staging table (the update target) has multiple rows which match the search predicate of the update.

-- This query will show you which of the cities (which have a match in the staging table)

-- are duped in the source table Country_Transform

-- Query uses WHERE EXISTS to...

Did you run the...

Looks like you have multiple matching rows in the country_transform table. Test with this:

SELECT *

FROM MOHG_Processing_TestData.dbo.Stage2test t

INNER JOIN (SELECT DISTINCT DQCorrection FROM MOHG_Processing_TestData.dbo.Country_Transform) s

ON t.Address1_City = s.DQCorrection...

kyle.doouss (12/2/2011)

---

Thanks for that. Something you might be able to help me with is if you look at a view in design it messes up the format.

Is there a...

kyle.doouss (12/2/2011)

---

Sorry to be a while coming back. I have had a problem with my web.

Execution plan attached. If you walk me through this one then it will...

DECLARE @Suffix VARCHAR(12)

SET @Suffix = ')'') AS DB2'

SELECT @Suffix