Mysql query select case when true insert

Question

does anybody know how to do this syntax below in mysql?

without Stored Procedure

and in single query only

---

SELECT CASE
 WHEN COUNT(v.value) = 0 THEN (
  INSERT INTO tbl_v (fid, uid, VALUE)
  SELECT fid, 1 AS uid, 'xxxxxx' AS VALUE FROM tbl_f
  WHERE category = 'categoryname' AND NAME = 'somevalue'
 )WHEN v.value <> 'test' THEN (
  'update syntax here' /* will be similar like insert statement above */
 )ELSE (
  v.value
 )END
FROM shared_tbl_f f
INNER JOIN tbl_v v ON f.fid = v.fid
WHERE v.uid = 1 AND f.category = 'categoryname' AND f.name = 'somevalue'

---

note:

there's no primary key on tbl_v the unique reference for tbl_v is only a combination from column fid and column uid.

because tbl_v is a mapping table (there's no primary key there) - fid and uid is a foreign key from another table

Answer 1

You can't, sorry. You have to do this in a stored procedure or in your applicationlogic.

Answer 2

You can use the ON DUPLICATE KEY UPDATE clause with your insert statement.

INSERT INTO tbl_v (fid, uid, VALUE)
  SELECT fid, 1 AS uid, 'xxxxxx' AS VALUE FROM tbl_f
  WHERE category = 'categoryname' AND NAME = 'somevalue'
ON DUPLICATE KEY UPDATE
  value = ?;

To get this to work their needs to be either a primary key or unique index on a column. A duplicate value will then start the update part of the statement.

Reference: http://dev.mysql.com/doc/refman/5.5/en/insert-select.html