CODEWITHSUNDEEP
mysql
Naithan
Naithan

Reputation: 25

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.

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'))

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'))

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

Thanks for borrowing your brains

Upvotes: 0

Views: 38

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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'))
)

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'))

Upvotes: 1

Related Questions