Mysql UPDATE 表取决于来自其他表的 NOT EXIST [英] Mysql UPDATE table depending on NOT EXIST from other table

问题描述

我之前曾尝试过回答这个问题，但可能是我问的太难了，所以重写了.

I have tried to get an answer to this before, but maybe I was asking it in a too difficult manner hence the rewrite.

我有两张桌子.我需要在 value_table 中搜索一个值，如果它找到了这个值，它必须搜索是否存在某些其他值.然后它必须从另一个表返回一个设置.

I have two tables. I need to search for a value in value_table and if it finds this value it has to search if certain other values exist. It then has to return a setting from the other table.

这是通过以下命令完成的:

This is done with the following command:

SELECT * 
FROM value_table AS t1 INNER JOIN settings_table ON settings_table.id=t1.id
WHERE certainvalue BETWEEN 0 AND 9999999999 
      AND anothervalue = 'some text'
      AND NOT EXISTS (SELECT 1
                      FROM value_table AS t2
                      WHERE t1.id = t2.id 
                            AND t2.certainvalue BETWEEN 0 AND 9999999999 
                            AND t2.anothervalue IN ('different text', 'some other different text'))

就像一个魅力.但是现在我想 SET 依赖于相同设置的设置表中的值.但这我无法做到.我猜这是我最好的尝试之一.

Works like a charm. However Now I would like to SET a value in the settings table dependent on the same setting. But this I cannot get it to do. This is one of my better tries I guess.

UPDATE settings_table
    SET setting = 1
    WHERE settings_table.id=values_table.id 
          AND values_table.lastupdate BETWEEN 0 AND 9999999999 
          AND values_table.listid = 206
          AND settings_table.eventtype = 'Sent an Email Campaign'
          AND NOT EXISTS (SELECT 1
                          FROM values_table AS t2
                          WHERE settings_table.id = t2.id 
                                AND t2.certainvalue BETWEEN 0 AND 9999999999 
                                AND t2.anothervalue IN ('different text', 'some other different text'))

我创建了一个 sqlfiddle 来展示更多我的意思.更新声明:http://sqlfiddle.com/#!9/0ee99/3和 select 语句:http://sqlfiddle.com/#!9/0ee99/1

I have created a sqlfiddle to show more what I mean. The update statement: http://sqlfiddle.com/#!9/0ee99/3 and the select statement: http://sqlfiddle.com/#!9/0ee99/1

谢谢你的脑洞

推荐答案

你需要使用 JOIN UPDATE 语法来解决这个问题

You need to use JOIN UPDATE syntax for this something as

UPDATE settings_table st
join value_table vt on st.id = vt.id
set st.setting = 1
where
vt.lastupdate BETWEEN 0 AND 9999999999 
AND vt.listid = 206
AND st.eventtype = 'Sent an Email Campaign'
AND NOT EXISTS(
  SELECT 1
  FROM values_table AS t2
  WHERE st.id = t2.id 
  AND t2.certainvalue BETWEEN 0 AND 9999999999 
  AND t2.anothervalue IN ('different text', 'some other different text'))
)

更新:由 jpw 创建的小提琴，内容如下

UPDATE: fiddle created by jpw with the following

UPDATE epaper_list_subscribers
JOIN epaper_list_subscriber_events 
ON epaper_list_subscriber_events.subscriberid=epaper_list_subscribers.subscriberid 
SET confirmed = 1     
WHERE     epaper_list_subscriber_events.lastupdate BETWEEN 0 AND 9999999999 
      AND epaper_list_subscriber_events.listid = 1
      AND epaper_list_subscriber_events.eventtype = 'Sent an Email Campaign'
      AND NOT EXISTS (SELECT 1
                      FROM epaper_list_subscriber_events AS t2
                      WHERE epaper_list_subscribers.subscriberid = t2.subscriberid 
                            AND t2.lastupdate BETWEEN 0 AND 9999999999 
                            AND t2.eventtype IN ('Opened an Email Campaign', 'Clicked on a link'))

这篇关于Mysql UPDATE 表取决于来自其他表的 NOT EXIST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！