Update a Field from a Join

[datamaan]

I was trying to find a solution to update a field "removewebzine" with a "T" but so far it works like two separte requests. Does anyone know what I can do to have it find a match then update the field?

SELECT IGC_EMAILSTATUS.EMAIL
FROM IGC_EMAILSTATUS
INNER JOIN
SYSDBA.CONTACT
ON IGC_EMAILSTATUS.EMAIL = SYSDBA.CONTACT.EMAIL

UPDATE
IGC_EMAILSTATUS
SET REMOVEWEBZINE = "T" [sig][/sig]

 

[THoey]

Would this work? I could try it myself, but the setup time would probably take longer than testing the query...

UPDATE IGC_EMAILSTATUS 
SET REMOVEWEBZINE = "T" 
WHERE IGC_EMAILSTATUS.EMAIL IN (
   SELECT IGC_EMAILSTATUS.EMAIL
   FROM IGC_EMAILSTATUS
   INNER JOIN SYSDBA.CONTACT ON 
   IGC_EMAILSTATUS.EMAIL = SYSDBA.CONTACT.EMAIL)

Hope it helps...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[datamaan]

Thank you as that did work!
Only problem now is I can not seem to link the data. I guess I need to have a line of code that copies the &quot;ClientID&quot; into the new table called IGC_EmailStatus.
This is very frustrating as all I wanted to do was update a remove or add to newsletter field with a T or F so I can handle my mailing list. [sig][/sig]

 

[THoey]

Create the field in that new table for the clientID, and then try this code:

UPDATE IGC_EMAILSTATUS (ClientID, REMOVEWEBZINE)
VALUES (IGC_EMAILSTATUS.ClientID, "T")
WHERE IGC_EMAILSTATUS.EMAIL IN (
   SELECT IGC_EMAILSTATUS.EMAIL
   FROM IGC_EMAILSTATUS
   INNER JOIN SYSDBA.CONTACT ON 
   IGC_EMAILSTATUS.EMAIL = SYSDBA.CONTACT.EMAIL)

Realize, I guessed where the clientID would come from for the VALUES clause. You need to make sure that is correct.

Hope that works...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]