Mysql查询select case when true insert

发布于 2024-08-23 17:21:23 字数 698 浏览 6 评论 0原文

有谁知道如何在 mysql 中执行下面的语法？

没有存储过程，并且仅在单个查询中

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'

注意：

tbl_v 上没有主键 tbl_v 的唯一引用只是列 fid 和列 uid 的组合。

因为 tbl_v 是一个映射表（那里没有主键） - fid 和 uid 是来自另一个表的外键。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柠檬色的秋千 2024-08-30 17:21:24

你不能，抱歉。您必须在存储过程或应用程序逻辑中执行此操作。

回复收藏 0 原文

祁梦 2024-08-30 17:21:23

您可以在插入语句中使用 ON DUPLICATE KEY UPDATE 子句。

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 = ?;

为了使其发挥作用，它们需要是列上的主键或唯一索引。然后，重复值将启动语句的更新部分。

参考： http://dev.mysql.com/doc/ refman/5.5/en/insert-select.html

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

回复收藏 0 原文

~没有更多了~