mysql - SQL Server : if record found update else insert, 合并查询错误更正

Question

我提出了以下 SQL Server 查询，用于更新行(如果找到则插入)

merge tblpermissions  as t
using (select cid, uid from tblpermissions) as s
on (t.cid = s.cid and t.uid=s.uid)
when not matched
THEN INSERT (cid, uid, [read], [write], [readonly], [modify], [admin]) VALUES ('1',   '1', 1, 1, 0, 1, 1) 
when matched 
THEN UPDATE SET [read]=1, write=1, readonly=0, modify=1, admin=1 ;

虽然它没有抛出任何错误，但它没有达到我的预期。表中没有记录，也没有插入新记录。

有更正吗？

编辑:考虑到您的建议，我对其进行了进一步修改，如下所示，但没有任何预期结果 -

MERGE INTO tblpermissions  as t
using (SELECT '1' AS cid, '1' AS uid FROM tblpermissions) as s
on (t.cid = s.cid and t.uid=s.uid)
WHEN NOT MATCHED 
THEN INSERT (cid, uid, [read], [write], [readonly], [modify], [admin])
VALUES  ('1', '1', 1, 1, 0, 1, 1) 
WHEN MATCHED
THEN UPDATE SET [read]=1, write=1, readonly=0, modify=1, admin=1 ;

编辑:请检查底部的评论，下面是对您的建议的改进查询，更不用说它不会按预期工作，因为它再次使用同一个表。

MERGE INTO tblpermissions  as t
using tblpermissions as s
on (t.cid = s.cid and t.uid=s.uid)
WHEN NOT MATCHED 
THEN INSERT (cid, uid, [read], [write], [readonly], [modify], [admin])
VALUES  ('1', '1', 1, 1, 0, 0, 1) 
WHEN MATCHED
THEN UPDATE SET [read]=1, write=1, readonly=0, modify=1, admin=1 ;

编辑:选择-如果找到更新-否则在下面插入替代项 -

$query = "SELECT * FROM ".SQL_PREFIX."permissions\n"
              ."WHERE cid='".$cid."' AND uid='".$uid."'";
    $sth = $this->dbh->query($query);
    $res = $sth->fetch();
    //print_r($res);var_dump($res);
    if(!$res || $res==null)
    {
        $query = "INSERT INTO ".SQL_PREFIX."permissions (cid, uid, [read], [write], [readonly], [modify], [admin])\n"
            ."VALUES ('$cid', '$uid', ".implode(", ", $values).")";
        if(!($sth = $this->dbh->query($query)))
            $this->db_error(__('Error inserting user permissions.'),
                $query);
    }else{
        $query = "UPDATE ".SQL_PREFIX."permissions SET ".implode(", ", $sets).";";
        if(!($sth = $this->dbh->query($query)))
            $this->db_error(__('Error updating user permissions.'),
                $query);
    } 

编辑:下面是针对这种情况的 mysql 解决方案 -

INSERT into tblpermissions (cid, uid, [read], [write], [readonly], [modify], [admin]) VALUES ('1', '1', 1, 1, 0, 0, 1) ON DUPLICATE KEY UPDATE [read]=1, write=1, readonly=0, modify=1, admin=1

它仅使用关键字 DUPLICATE KEY。还要再次强调的是，cid 和 uid 组合有独特的键约束。所以 mysql 所做的是，首先它只是查找我们尝试插入的 cid 和 uid 的组合，如果找到则更新它，否则插入新记录。

Answer 1

由于源表和目标表相同，因此永远不会出现行不匹配的情况。 tblpermissions 上的所有行都将被更新。

为了插入，源表(用 using 定义)必须与目标表不同，例如以下示例:

merge tblpermissions  as t
using (select cid, uid from otherTable) as s
on (t.cid = s.cid and t.uid = s.uid)

merge tblpermissions  as t
using (select cid, uid from tblpermissions) as s
on (t.cid = s.cid + 1 and t.uid = s.uid + 1)

编辑:由于您使用的是常量值而不是从任何表中选择的结果，因此也许以下内容可以解决问题:

merge tblpermissions as t
using (select '1' cid, '1' uid, 1 [read], 1 [write], 0 [readonly], 0 [modify], 1 [admin]) s
on (t.cid = s.cid and t.uid = s.uid)
when matched 
    then update set [read] = s.[read], write = s.[write], readonly = s.[readonly], modify = s.[modify], admin = s.[admin]
when not matched
    then insert (cid, uid, [read], [write], [readonly], [modify], [admin]) values (s.cid, s.uid, s.[read], s.[write], s.[readonly], s.[modify], s.[admin]);

虽然我认为只插入一行合并有点矫枉过正，但执行以下操作会更容易:

if exists (select 1 from tblpermissions where cid = '1' and uid = '1')
update tblpermissions set [read] = 1, write = 1, readonly = 0, modify = 1, admin = 1 where cid = '1' and uid = '1'
else
    insert tblpermissions (cid, uid, [read], [write], [readonly], [modify], [admin]) VALUES ('1', '1', 1, 1, 0, 1, 1)

我认为 SQL Server 没有更短或更优雅的方法来执行此操作，与 MySQL 中的on重复不同。