Help with Update Query

[JVZ]

Need some help writing a query that will update a fields based on a lookup. The problem I’m having is related to how to perform the lookup table.

The based table looks something like this.

Stg_CustProfile:
RecordId INT,
EmailAddr VARCHAR(100),
Firstname VARCHAR(100),
Lastname VARCHAR(100),
PhoneNumber VARCHAR(100),
… Other profile related fields

Sample Data:

Stg_CustProfile:

RecordId EmailAddr Firstname LastName PhoneNumber
1 abc@123.com Joe Sample 123-456-7890
2 abc@124.com Joe2 Sample 2 123-456-7891
3 abc@125.com Joe3 Sample 3 123-456-7892
…

Look up (table Name: admin_Suppression)

SuppressionID
SuppressionField
SuppressionValue
… Other data fields

Sample Data:

Admin_Suppression

SuppressionID SuppressionField SuppressionValue
1 emailAddr abc@123.com
2 PhoneNumber 123-456-7891


The query that I need help with, will update a field (lets call it isSuppressed) in the stg_CustProfile table if a match is found (based on the suppression field mapping to based table). The only solution that I came up with so far was to construct a dynamic sql statement… any advise on a better solution?

 

[SQLSister]

No don;t use dynamic sql
use an update statement that uses a join to the look up table

sample sysntax:

update table1
set field3 = t2.field3
from table1 t1 join table2 t2 on t1.field1 = t2.field2 
where t1.field3 is null

Questions about posting. See faq183-874