Trouble with update statement

[cb49747]

I have two tables with common fields (id number and email) I would like to write an update statement that would place the value for field email in table one into field email in table two if field email is empty in table 2 and there is a value in field email in table one.

This is what I tried and it failed miserably.

 UPDATE enrollments SET enrollments.email = einfo.email LEFT JOIN einfo ON einfo.idnumber = enrollments.idnumber WHERE enrollments.email = '' AND einfo.email != ''

any one have any idea on how to do this?

Thanks

 

[ChrisHunt]

Something like this?

UPDATE enrollments
SET    email = (SELECT einfo.email
                FROM   einfo
                WHERE  einfo.idnumber = enrollments.idnumber)
WHERE  email IS NULL

That said, it's generally a bad idea to have the same piece of information in more than one table. Are you sure you want to do it?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ingresman]

That said, it's generally a bad idea to have the same piece of information in more than one table.

Fair point but it denormilastion in this way is an accepted technique.

 

[cb49747]

Thanks, I will give this a try.

That said, it's generally a bad idea to have the same piece of information in more than one table.

This is correct. My goal is to get rid of the email field in the einfo table as it is redundant. but before I do I need to make sure the information is moved.