So I'm having a problem with an update query. I have three tables:
Table: A
Columns: acnt, name, company, email, domain
Table: AM
Columns: acnt, m_id
Table: M
Columns: m_id, name, company, email, domain
and I want to conditionally update the columns in one to values from the
other. i.e., I want to put the value of A.name into M.name, but only
if M.name is currently NULL, AND A.name has a usable value (not an empty
string).
This is what I came up with, but it doesn't work - it only replaces the
values where the column in M is not null.
update A
join AM on A.acnt = AM.acnt
join M on AM.m_id = M.m_id
SET M.name = IF( (!M.name AND A.name != ''), A.name, M.name),
M.company = IF( (!M.company AND A.company != ''), A.company, M.company),
M.email = IF( (!M.email AND A.email != ''), A.email, M.email),
M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain)
Any thoughts?
THanks,
andy
--
Andy Wallace
iHOUSEweb, Inc.
[email protected]
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
