执行 UPDATE 查询时检查值是否存在

发布于 2024-10-12 10:07:59 字数 484 浏览 3 评论 0原文

我需要执行一个简单的更新查询，只有在没有具有更新值（item_name）的值时才应执行更新。例如，

数据库表 [item_types]

item_id(PK) | item_name

假设存在 item_id 为 6，我的尝试是

UPDATE item_types as k SET k.item_name = 'item_1' WHERE NOT EXISTS (SELECT * FROM item_types as a WHERE a.item_name = 'item_1') AND k.item_id = '6'

但这给了我错误

"You can't specify target table 'k' for update in FROM clause"

您能否解释一下这里的错误以及如何实现此目的？

谢谢

原文

I need to perform a simple update query where the update should only be done, if there is no value with updating value (item_name). For example,

DB table [item_types]

item_id(PK) | item_name

Assuming there exist item_id with 6, My attempt is

UPDATE item_types as k SET k.item_name = 'item_1' WHERE NOT EXISTS (SELECT * FROM item_types as a WHERE a.item_name = 'item_1') AND k.item_id = '6'

But this gives me error

"You can't specify target table 'k' for update in FROM clause"

Could you please explain the error here and how can I achieve this ?

Thank you

分享到QQ

分享到微博

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

发布评论

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

我是男神闪亮亮 2024-10-19 10:07:59

您可以对 item_name 字段施加唯一的限制并捕获错误。

回复收藏 0 原文

神仙妹妹 2024-10-19 10:07:59

我认为您不能在插入语句中使用表的别名。也可以使用 , 而不是 AND。

尝试

UPDATE item_types SET item_name = 'item_1' WHERE NOT EXISTS (SELECT * FROM 
item_types as a WHERE a.item_name = 'item_1') , item_id = '6'

I dont think you can use an alias for a table in an insert statement. Also use , rather than AND.

Try

UPDATE item_types SET item_name = 'item_1' WHERE NOT EXISTS (SELECT * FROM 
item_types as a WHERE a.item_name = 'item_1') , item_id = '6'

回复收藏 0 原文

吃不饱 2024-10-19 10:07:59

正如托比建议的，您不能在 UPDATE 行中使用别名。尝试以下语法：

UPDATE item_types 
SET item_name = 'item_1' 
FROM item_types k
WHERE NOT EXISTS (SELECT * FROM item_types as a WHERE a.item_name = 'item_1') 
AND k.item_id = '6'

hth, R

As Toby suggested, you can't use an alias in the UPDATE line. Try the following syntax though:

UPDATE item_types 
SET item_name = 'item_1' 
FROM item_types k
WHERE NOT EXISTS (SELECT * FROM item_types as a WHERE a.item_name = 'item_1') 
AND k.item_id = '6'

hth, R

回复收藏 0 原文

~没有更多了~