基于相同KODAS值更新STOTMARS表指定行的EILNR字段

Let's break down what's going wrong here and fix your update statement.

The Problem #

You're trying to update EILNR values for rows where PUNKTAS = 'Dvaro st.3' to match the EILNR from rows with the same KODAS and MARS where PUNKTAS = 'Centro st.1'. However, your current query is throwing a validation error because it's attempting to set EILNR to NULL—which violates the column's NOT NULL constraint.

Why Your Query Fails #

Looking at your subquery:

select b.EILNR from STOTMARS b where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS

The condition a.PUNKTAS = 'Centro st.1' will never be true here, because your outer WHERE clause targets rows where a.PUNKTAS = 'Dvaro st.3'. This means the subquery returns no results for every row you're trying to update, resulting in a NULL value that can't be assigned to EILNR.

Correct Solutions #

Here are two working approaches to achieve your goal:

1. Corrected Subquery with Existence Check

This fixes the subquery logic and adds an EXISTS clause to ensure we only update rows that have a matching Centro st.1 entry (avoiding accidental NULL assignments):

UPDATE STOTMARS a
SET a.EILNR = (
    SELECT b.EILNR
    FROM STOTMARS b
    WHERE b.KODAS = a.KODAS
      AND b.MARS = a.MARS
      AND b.PUNKTAS = 'Centro st.1'
)
WHERE a.PUNKTAS = 'Dvaro st.3'
AND EXISTS (
    SELECT 1
    FROM STOTMARS b
    WHERE b.KODAS = a.KODAS
      AND b.MARS = a.MARS
      AND b.PUNKTAS = 'Centro st.1'
);

2. JOIN-Based Update (Firebird-Compatible)

If you prefer a more readable join syntax (supported in Firebird 2.1+), this directly links the rows you want to update to their matching source rows:

UPDATE STOTMARS a
SET a.EILNR = b.EILNR
FROM STOTMARS b
WHERE a.PUNKTAS = 'Dvaro st.3'
  AND b.PUNKTAS = 'Centro st.1'
  AND a.KODAS = b.KODAS
  AND a.MARS = b.MARS;

Key Notes #

• Both queries use KODAS and MARS to match rows, which aligns with your sample join query's intent.
• The EXISTS clause in the first approach is optional but recommended—it prevents updating rows where no matching Centro st.1 entry exists, which would otherwise cause the same NULL error.

内容的提问来源于stack exchange，提问作者Haruki