List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:September 16 2010 6:04pm
Subject:RE: Update query problem
View as plain text  
Try using the IS NULL operator instead of !

-Travis

-----Original Message-----
From: Andy Wallace [mailto:awallace@stripped] 
Sent: Thursday, September 16, 2010 10:47 AM
To: mysql@stripped
Subject: Update query problem

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.
awallace@stripped
(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/mysql?unsub=1


Thread
skip lockingmonloi perez12 Sep
  • Re: skip lockingMichael Satterwhite12 Sep
    • Re: skip lockingmonloi perez16 Sep
      • Re: skip lockingNilton Moura16 Sep
        • Update query problemAndy Wallace16 Sep
          • RE: Update query problemTravis Ard16 Sep
Re: skip lockingNilton Moura18 Sep