mysql - SQL Server : if record found update else insert, merge query error correction -

i have come following query sql server updating row if found else insert

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

although not through error, not achieving expect. there's no record in table , not inserting new record.

any correction?

edit: considering suggestions have modified further below,without expected result though -

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

edit: pls check comment @ bottom, below improved query on suggestions, not mention wont work expected takes same table again.

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

edit: select-if found update-else insert alternative below -

$query = "select * ".sql_prefix."permissions\n"               ."where cid='".$cid."' , uid='".$uid."'";     $sth = $this->dbh->query($query);     $res = $sth->fetch();     //print_r($res);var_dump($res);     if(!$res || $res==null)     {         $query = "insert ".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);     }  

edit: below mysql solution situation -

insert 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

which employs keyword duplicate key. highlight again that, there's unique key constraint on cid & uid combination. mysql is, first looks combination of cid & uid trying insert, if found updates else inserts fresh record.

because source , target tables same, there never case of rows not matching. rows on tblpermissions being updated.

in order insert, source table (defined using) has different target table, such following examples:

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

edit: since you're using constant values instead of result of select table, maybe following trick:

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

although think insert 1 row merge overkill, easier following:

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

i don't think sql server has shorter or more elegant way perform this, nothing on duplicate mysql.