Mysql IF NOT EXISTS then 语法

发布于 2024-12-04 10:17:42 字数 395 浏览 1 评论 0原文

我现在无法根据我的需要生成一个有效的 mysql 查询。我已经接近

IF NOT EXISTS(SELECT * 
              FROM Users 
              WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4') 
UPDATE Users 
SET `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE `User`='andrewfree';

“This failed”，我正在阅读有关 INSERT IGNORE INTO 的内容，但这也没有帮助。我想要做的就是将此哈希添加到用户下载字段（如果任何人下载字段的表中不存在该哈希）。

原文

I can't manage to produce a working mysql query right now for what I want. I got as close as

IF NOT EXISTS(SELECT * 
              FROM Users 
              WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4') 
UPDATE Users 
SET `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE `User`='andrewfree';

This fails, I was reading about INSERT IGNORE INTO but that hasn't helped either. What I am trying to do is add this hash to a users Download field if it doesn't exist in the table in anyones Download field.

分享到QQ

分享到微博

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

发布评论

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

梦旅人picnic 2024-12-11 10:17:42

移动 WHERE 子句中的 EXISTS：

UPDATE Users
SET    `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
WHERE  `User`='andrewfree'
AND    NOT EXISTS(
           SELECT 1
           FROM   Users
           WHERE  Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
       )

或者在 Users.Downloads 上添加唯一键并使用 UPDATE IGNORE：

ALTER TABLE Users ADD UNIQUE KEY downloads_unique (Downloads);

UPDATE IGNORE Users SET Downloads = 'c63...' WHERE User='andrewfree';

如果此 Downloads 值，更新将不会完成。

使用 IGNORE 关键字，[...]发生重复键冲突的行不会更新。

请参阅更新语法。

Move the EXISTS in the WHERE clause:

UPDATE Users
SET    `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
WHERE  `User`='andrewfree'
AND    NOT EXISTS(
           SELECT 1
           FROM   Users
           WHERE  Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
       )

Or add a unique key on Users.Downloads and use UPDATE IGNORE:

ALTER TABLE Users ADD UNIQUE KEY downloads_unique (Downloads);

UPDATE IGNORE Users SET Downloads = 'c63...' WHERE User='andrewfree';

If a row already exists with this Downloads value, the UPDATE will not be done.

With the IGNORE keyword, [...] Rows for which duplicate-key conflicts occur are not updated.

See UPDATE syntax.

回复收藏 0 原文

深海夜未眠 2024-12-11 10:17:42

UPDATE Users 
SET `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE `User`='andrewfree'
AND NOT EXISTS(SELECT * 
              FROM Users 
              WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4')

编辑：

实际上，上面的语句会抛出错误。我认为这可以做到（未经测试）

UPDATE Users u1 INNER JOIN Users u2 
                ON u2.Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
SET u1.`Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE u1.`User`='andrewfree'

UPDATE Users 
SET `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE `User`='andrewfree'
AND NOT EXISTS(SELECT * 
              FROM Users 
              WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4')

EDIT:

Actually, the above statement will throw an error. I think this will do it (untested)

UPDATE Users u1 INNER JOIN Users u2 
                ON u2.Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
SET u1.`Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE u1.`User`='andrewfree'

回复收藏 0 原文

~没有更多了~