What is wrong with my UPDATE statement WHERE NOT EXISTS?

Question

What I am trying to accomplish is to update the ISCURRENT field to 'N' and the EFFECTIVE_END_DATE field to the current date if the record of its type does not have the most recent EFFECTIVE_START_DATE.
An error does not get thrown it just tells me "0 rows affected" but I created a record with a more recent EFFECTIVE_START_DATE which should affect the other record in the table that has the earlier EFFECTIVE_START_DATE.
Here is an image of the 2 records I'm using to test it out.

The record that has a KTEXT of '400 Atlantic' should be changed from this script to have an ISCURRENT ='N' and EFFECTIVE_END_DATE=GETDATE() because the record with the KTEXT of 500 Maria has a more recent EFFECTIVE_START_DATE

UPDATE [SAP].[src_gl_sap_m_cepct]
set    ISCURRENT='N', 
       EFFECTIVE_END_DATE=GETDATE()
WHERE NOT EXISTS (SELECT [SPRAS],
                         [PRCTR],
                         MAX(EFFECTIVE_START_DATE) 
                  FROM  [SAP].[src_gl_sap_m_cepct] AS A 
                  WHERE CONCAT([SAP].[src_gl_sap_m_cepct].[SPRAS],[SAP].[src_gl_sap_m_cepct].[PRCTR]) = CONCAT(A.[SPRAS],A.[PRCTR]
                 )
 GROUP BY [SPRAS],[PRCTR]);

Thank you !

Do you get an error ? Or does the update not happens ? What seems to be the problem here ? — GuidoG, Feb 24 '21 at 13:50
Sorry - I updated the description I don't get an error it just says " 0 rows affected" but there should be one row affected I tried to put a screen shot of the sample data — mb1002, Feb 24 '21 at 13:54
@mb1002 - screenshots of sample data aren't useful - we don't need the transcription/typing practice. Please add sample data (ideally formatted as `INSERT` statements) as *text* that we can copy and paste into our databases to recreate the issue. — Damien_The_Unbeliever, Feb 24 '21 at 13:59
If no rows are effected, then your `WHERE` is resulting in no rows. Change the `UPDATE` statement to a `SELECT` stateemnt with the same criteria and you'll get no rows. Also [3+ part naming is due to be deprecated](https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/) from SQL Server; stick to aliasing your objects and use that alias to qualify your column(s). It'll make code far more succinct too. — Thom A, Feb 24 '21 at 14:00
@mb1002 Please thank the person who turned your wall of code text into something readable. That should be something you do. And we know nothing of your schema and the logical key that you use for your matching purposes. It is even more difficult to understand when your table and columns names are cryptic and you concatenate columns together in order to correlate. That is just logically wrong and inefficient - join on the columns directly. — SMor, Feb 24 '21 at 14:11
And your code is logically flawed. In a self join, a given row will match itself so "not exists" will always be false. The general approach is the same as finding duplicates - like [this](https://stackoverflow.com/questions/28765682/sql-server-group-by-query-select-first-row-each-group). If you can get the "first" (or last - in your case), then you can update it (and the others if you so desire). — SMor, Feb 24 '21 at 14:15

GuidoG · Accepted Answer · 2021-02-24T14:31:03.767

Correct me if I am wrong, but this part of your query

FROM  [SAP].[src_gl_sap_m_cepct] AS A 
WHERE CONCAT([SAP].[src_gl_sap_m_cepct].[SPRAS],[SAP].[src_gl_sap_m_cepct].[PRCTR]) = CONCAT(A.[SPRAS],A.[PRCTR]

can also be written like this (because you have a self join)

FROM  [SAP].[src_gl_sap_m_cepct] AS A 
WHERE CONCAT(A.[SPRAS], A.[PRCTR]) = CONCAT(A.[SPRAS], A.[PRCTR]

And like this I notice that you are simply comparing a value to the same value again.
Thus this will always evaluate as TRUE
And thus the not exists clause will never evaluate as true
And therefore no updates will happen.

I think something like this might work for you

UPDATE c
set    c.ISCURRENT='N', 
       c.EFFECTIVE_END_DATE = GETDATE()
FROM   SAP.src_gl_sap_m_cepct c
WHERE  EXISTS ( select 1
                FROM   SAP.src_gl_sap_m_cepct AS A
                WHERE  CONCAT(c.SPRAS, c.PRCTR) = CONCAT(A.SPRAS, A.PRCTR)
                AND    A.EFFECTIVE_START_DATE > c.EFFECTIVE_START_DATE
              )

No problem, glad I could help. – GuidoG Feb 24 '21 at 14:42 — GuidoG, Feb 24 '21 at 14:42

Denis Rubashkin · Answer 2 · 2021-02-24T14:36:39.893

0

If I understood correctly, the statement should be like this:

UPDATE c
set    ISCURRENT='N', 
       EFFECTIVE_END_DATE = GETDATE()
FROM [SAP].[src_gl_sap_m_cepct] c
WHERE EXISTS (
        SELECT 1 
        FROM  [SAP].[src_gl_sap_m_cepct] AS A 
        WHERE CONCAT(c.[SPRAS], c.[PRCTR]) = CONCAT(A.[SPRAS],A.[PRCTR])
            AND c.EFFECTIVE_START_DATE < A.EFFECTIVE_START_DATE 
);

edited Feb 24 '21 at 14:36

answered Feb 24 '21 at 14:13

Denis Rubashkin

2,151
1
9
14

Unfortunately this still returns "0 rows affected" – mb1002 Feb 24 '21 at 14:24

What is wrong with my UPDATE statement WHERE NOT EXISTS?

2 Answers2