gpt4 book ai didi

php - SQL:如何对两个表使用 REPLACE INTO 并且仅当特定值匹配时?

转载 作者:行者123 更新时间:2023-11-30 21:35:31 25 4
gpt4 key购买 nike

我有两个具有相同列和 ID 的表。表 1 包含主要记录。表 2 包含更新记录和新记录。

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')

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

但最终我无法在这两种情况下使用它。

我错过了什么?我对函数 WHERE 和 EXISTS/NOT EXISTS 的工作方式有什么误解吗?有更好的选择吗?

最佳答案

经过无数天的研究 MySQL 手册和 Stack Overflow 答案,我终于想出了一个可行的解决方案。

我现在有两个问题。一个用于更新现有记录:

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'

还有一个用于添加新记录:

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

接下来我将注意防止 SQL 注入(inject)漏洞。感谢大家对这个问题的帮助和提示!

关于php - SQL:如何对两个表使用 REPLACE INTO 并且仅当特定值匹配时?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54041195/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com