CODEWITHSUNDEEP
sqlpostgresql
Sovakah
Sovakah

Reputation: 41

Postgresql Add WHERE clauses in an UPDATE request

I intend to write a SQL request UPDATE and I'd like to verify if table2.fld5 already contains 'Hello' in order to not write it several times, but didn't find a way to achieve this verification.

The request is :

UPDATE table2
    SET fld5 = concat(fld5, 'Hello')
    WHERE NOT EXISTS (SELECT *
                      FROM table1
                      WHERE table1.fld1 = table2.fld1 AND
                            table1.fld2 = table2.fld2 AND
                            table1.fld3 = table2.fld3
                     )
     AND table2.fld4 > (NOW() - INTERVAL '79 MINUTE');

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

How about adding the condition to the where:

UPDATE table2
    SET fld5 = concat(fld5, 'Hello')
    WHERE (fld5 not like '%Hello' OR fld5 IS NULL) AND  -- perhaps this should be '%Hello%'
          NOT EXISTS (SELECT *
                      FROM table1
                      WHERE table1.fld1 = table2.fld1 AND
                            table1.fld2 = table2.fld2 AND
                            table1.fld3 = table2.fld3 AND
                            table2.fld4 > (NOW() - INTERVAL '79 MINUTE')
                     );

I agree with the comment about the fld4 comparison. I think this is the logic:

UPDATE table2
    SET fld5 = concat(fld5, 'Hello')
    WHERE (table2.fld5 not like '%Hello' OR table2.fld5 IS NULL) AND  -- perhaps this should be '%Hello%' AND
          table2.fld4 > (NOW() - INTERVAL '79 MINUTE') AND  -- or should this be <
          NOT EXISTS (SELECT *
                      FROM table1
                      WHERE table1.fld1 = table2.fld1 AND
                            table1.fld2 = table2.fld2 AND
                            table1.fld3 = table2.fld3                                
                     );

Upvotes: 1

Succenna
Succenna

Reputation: 25

Perhaps your date check needs to be outside of the exists subquery:

UPDATE table2 SET fld5 = concat(fld5, 'Hello') 
WHERE fld5 NOT LIKE '%Hello%' AND table2.fld4 > (NOW() - INTERVAL '79 MINUTE'
AND NOT EXISTS (SELECT * FROM table1 WHERE table1.fld1 = table2.fld1 AND table1.fld2 = table2.fld2 AND table1.fld3 = table2.fld3))

;

Upvotes: 1

Related Questions