MySQL: conditional REPLACE

[csdude55]

I'm hoping you guys can help me logic this out.

Here's my current query:

SET @var_user = 'example';

REPLACE INTO tableA
 SELECT username, colA, colB FROM tableB
  WHERE username=@var_user
 LIMIT 1;

I'm wanting to change it so that IF @var_user already exists in tableA then it will only replace if tableB.updated > tableA.updated. In both tables "updated" is a TIMESTAMP column, and in tableA it's set to "on update CURRENT_TIMESTAMP".

I'm guessing that it will go something like this:

SET @var_user = 'example';

REPLACE INTO tableA
 SELECT username, colA, colB FROM tableB
  WHERE username=@var_user
   AND tableB.updated > tableA.updated
 LIMIT 1;

But if "username" doesn't already exist in tableA, would this still insert? And if the SELECT doesn't return anything, would it still update the "updated" column (which I definitely wouldn't not want)?

[topr8]

you can use 'IF' statements in mySQL

[csdude55]

I haven't fully tested it yet, but it looks like this will work:

SET @var_user = 'example';

REPLACE INTO tableA
 SELECT tableB.username, tableB.colA, tableB.colB FROM tableB
 LEFT JOIN tableA
  ON tableB.username = tableA.username
 WHERE username=@var_user
  AND
   (
    tableB.updated > tableA.updated OR
    COALESCE(tableA.updated, 0) = 0
   )
 LIMIT 1;

I only tested the SELECT part, not the REPLACE part. But it returned what I expected both when I had something in tableB but not tableA, and when tableB.updated > tableA.updated.