Welcome to WebmasterWorld Guest from 3.92.92.168

Forum Moderators: open

Message Too Old, No Replies

MySQL: conditional REPLACE

     
5:03 am on Nov 3, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120


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)?
6:58 am on Nov 3, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3512
votes: 86


you can use 'IF' statements in mySQL
9:14 pm on Nov 5, 2018 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1205
votes: 120


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.