Update Inner Join

[KDavie]

I apologize for asking a question that has been presented all too many times. However, after reading several threads and google hits, I can't figure out what I am doing wrong.

I have two tables, a customer table and an inquiry table. I need to update customer records based on a value in the inquiry table. My SQL looks like this:

UPDATE Customers
SET opt_in_contact = 'N',
    opt_in_mail = 'N',
    opt_in_email = 'N'
FROM Customers INNER JOIN Inquiries ON Inquiries.is_customer_id = Customers.is_customer_id
WHERE Inquiries.inquiry_type = 'un-subscription'

I have tried other variations of this, such as:

UPDATE Customers
SET opt_in_contact = 'N',
    opt_in_mail = 'N',
    opt_in_email = 'N'
FROM Inquiries
WHERE Customers.is_customer_id  = Inquiries.is_customer_id AND Inquiries.inquiry_type = 'un-subscription'

But no matter what I do, all records in the customers table are getting updated. How can I limit the update based on the join condition and the where clause?

Any help would be appreciated.

-Kevin

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[bborissov]

Did you try:

UPDATE Customers
SET opt_in_contact = 'N',
    opt_in_mail = 'N',
    opt_in_email = 'N'
FROM Customers
     INNER JOIN Inquiries ON Inquiries.is_customer_id = Customers.is_customer_id AND
                Inquiries.inquiry_type = 'un-subscription'

Also what that gives to you:

SELECT *
FROM Customers
     INNER JOIN Inquiries ON Inquiries.is_customer_id = Customers.is_customer_id AND
                Inquiries.inquiry_type = 'un-subscription'

You always could test your UPDATEs if you change UPDATE clause to SELECT one, then viw the result set and consider if that works for you.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[KDavie]

I tried your suggestion but am still encountering the same issue, all records are being updated.

The selects statements I use to validate the join conditions and where clauses work fine. It's only when I try to update that it doesn't seem to work.

-Kevin

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com

 

[bborissov]

No way. If the SELECT works as expected that UPDATE must be correct.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[KDavie]

Nevermind, the issue was with the data itself. There were multiple records with the same customer_id with various inquiry types. The t-sql itself works fine.

Thanks,

-Kevin

Kevin Davie
Consultant
Sogeti USA

http://www.us.sogeti.com