SQL: How to use REPLACE INTO with two tables and only if specific values match?

Question

I have two tables with the same columns and ids. Table 1 contains the main records. Table 2 contains updated records and new records.

Table 1:    ID | STATUS   | CONTENT               | etc..
            1  | open     | value can be modified |
            2  | pending  | value is almost final |
            3  | answered | value is final        |

Table 2:    ID | STATUS   | CONTENT               | etc..
            1  | open     | value has new data    |
            2  | open     | value is default      |
            3  | open     | value is default      |
            4  | open     | value is default      |

Desired:    ID | STATUS   | CONTENT               | etc..
            1  | open     | value has new data    |
            2  | pending  | value is almost final |
            3  | answered | value is final        |
            4  | open     | value is default      |

I'd like to merge the records from table 2 into table 1 using REPLACE INTO. There are two cases for each record:

1) if the table 1 value of column "status" is not "pending" and not "answered", the whole record should be overwritten with its equivalent from table 2

OR

2) if the record doesn't exist in table 1, the record should be added to it.

Because I just started working on my first code that involves MySQL, I tried modified versions of this solution and this solution and came up with

REPLACE INTO $table
            SELECT * FROM $newtable
            WHERE NOT EXISTS(
            SELECT *
            FROM $table
            WHERE $table.status = 'pending' OR $table.status = 'answered')

and

REPLACE INTO $table
        SELECT *
        FROM $newtable t1
        WHERE EXISTS(
        SELECT *
        FROM $table t2
        WHERE t2.status = t1.status)

but in the end I couldn't get it to work in both cases.

What am I missing? Did I get the wrong idea of how the functions WHERE and EXISTS/NOT EXISTS work? Are there better alternatives?

[must read](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) for you. — Raymond Nijland, Jan 04 '19 at 14:52
@RaymondNijland What's the link with the question ? (Even if it's a good link anyway). EDIT: Oh, because it's the first time he uses Mysql and PHP together, my bad ^^ — Julien B., Jan 04 '19 at 14:53
"What's the link with the question ?" @JulienB php variables in the SQL query?. possible SQL injections? that's the link. — Raymond Nijland, Jan 04 '19 at 14:55
@RaymondNijland Yeah totally, i thought it was written like that for simplification purposes ^^ — Julien B., Jan 04 '19 at 14:56
Mysql "insert on duplicate key update" would be perfect for this — erik258, Jan 04 '19 at 14:57
"I have two tables with the same columns and ids. Table 1 contains the main records. Table 2 contains updated records and new records." I wonder why you have two tables annyway with the same structure because it's not really needed besides it's make things harder because you can use only one table for this just fine. — Raymond Nijland, Jan 04 '19 at 14:59
@RaymondNijland thanks a lot for the link, helps me to understand the relationship between php and SQL. — Cellsar, Jan 04 '19 at 15:28
@RaymondNijland My system gives every user its own table ( = Table 1). These tables are meant for user input. On the other hand there's a master table ( = Table 2) that can be modified by the admin only. The function I am struggling with should allow to push the record updates from the master table to all individual user tables but without overwriting the changes each user has already made to it's own records. — Cellsar, Jan 04 '19 at 15:28
@Cellsar : Yes you can follow the same approach with an update clause but you would need to specify columns names and so on. — Julien B., Jan 04 '19 at 15:32

Lajos Arpad · Answer 1 · 2019-01-06T12:12:33.500

0

So, you want to do a replace into if status is not pending and not answered. So, something like this looks like a good way to solve the problem (untested):

replace into destinationTable
select * from
(
    select it2.*
    from sourceTable it1
    left join destinationTable it2
    on it1.ID = it2.ID and it1.status not in ('pending', 'answered')
) t

I used different table names on purpose, because your variables are prone to SQL injection.

edited Jan 06 '19 at 12:12

answered Jan 04 '19 at 15:16

Lajos Arpad

64,414
37
100
175

I tested this but it doesn't change anything :( – Cellsar Jan 04 '19 at 21:25
No, it even says the query was successful – Cellsar Jan 05 '19 at 11:07
@Cellsar if you execute the query without the first row, starting with the select, what is the result? – Lajos Arpad Jan 06 '19 at 12:13

score 0 · Accepted Answer · answered Jan 05 '19 at 11:19

After countless days of studying the MySQL manual and Stack Overflow answers I finally came up with a working solution.

I now have two queries. One for updating existing records:

UPDATE $table
INNER JOIN `$newtable` ON $table.id=$newtable.id
SET $table.status=$newtable.status,
    $table.content=$newtable.content
WHERE $table.status = 'open'
  OR $table.status = 'hold'

and one for adding new records:

INSERT INTO `$table` (id, status, content)
  SELECT
    $newtable.id,
    $newtable.status,
    $newtable.content
  FROM `$newtable`
  ON DUPLICATE KEY UPDATE $table.status=$table.status;

I'll take care of preventing SQL injection vulnerability next. Thanks to all for your help and hints with this issue!

SQL: How to use REPLACE INTO with two tables and only if specific values match?

2 Answers2