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

问题: 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...

问题:

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?


回答1:

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!


回答2:

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.

  • 发表于 2019-01-09 23:46
  • 阅读 ( 233 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除