CODEWITHSUNDEEP
sqlsql-updatefirebird
Haruki
Haruki

Reputation: 109

Update column value with value from another row where specific column is the same

I have a table:

CREATE TABLE STOTMARS
(
  KODAS integer NOT NULL,
  PUNKTAS varchar(50) NOT NULL,
  MARS varchar(10) NOT NULL,
  EILNR integer NOT NULL,
  METRAI integer NOT NULL,
  VIDGR integer NOT NULL,
  TARPINIS varchar(1),
  CONSTRAINT STOTMARS_ID PRIMARY KEY (KODAS,PUNKTAS,MARS,EILNR)
);
CREATE INDEX STOTMARS_EILNR ON STOTMARS (EILNR);
CREATE INDEX STOTMARS_KODAS ON STOTMARS (KODAS);
CREATE INDEX STOTMARS_MARS ON STOTMARS (MARS);
CREATE INDEX STOTMARS_PUNKTAS ON STOTMARS (PUNKTAS);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON STOTMARS TO  SYSDBA WITH GRANT OPTION;

I need to update value of EILNR in rows where PUNKTAS = 'Dvaro st.3' with EILNR value from row with the same KODAS value and PUNKTAS value of 'Centro st.1'

So far I tried:

update STOTMARS a
set a.EILNR = (
    select
        b.EILNR
    from STOTMARS b
    where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS
)
where a.PUNKTAS = 'Dvaro st.3'

but this gives me error:

Executing statement...
Error: *** IBPP::SQLException ***
Context: Statement::Execute( update STOTMARS a
set a.EILNR = (
    select
        b.EILNR
    from STOTMARS b
    where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS
)
where a.PUNKTAS = 'Dvaro st.3' )
Message: isc_dsql_execute2 failed

SQL Message : -625
The insert failed because a column definition includes validation constraints.

Engine Code    : 335544347
Engine Message :
validation error for column EILNR, value "*** null ***"


Total execution time: 0.000s

To make it more clear query:

select
a.MARS, a.EILNR, b.EILNR
from STOTMARS a
join STOTMARS b
on a.MARS = b.MARS and b.PUNKTAS = 'Dvaro st.3'
where a.PUNKTAS = 'Centro st.1'

gives me a table where each third column value should be replace with that row's second column value.

Upvotes: 1

Views: 1277

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109016

The problem is that the inner-select yields no rows, and therefor the update tries to assign null, because the conditions applied are mutually exclusive, and this fails because of a NOT NULL constraint on EILNR:

update STOTMARS a
set a.EILNR = (
    select
        b.EILNR
    from STOTMARS b
    where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS
)
where a.PUNKTAS = 'Dvaro st.3'

a.PUNKTAS can't be 'Centro st.1' and 'Dvaro st.3' at the same time. The condition in the inner-select should probably use b.PUNKTAS:

where b.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS

You might also want to consider using MERGE instead of this correlated update.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can avoid NULL in several ways. One way is to replace the value with an appropriate value:

update STOTMARS a
    set a.EILNR = (select coalesce(max(b.EILNR), 0)
                   from STOTMARS b
                   where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS
                  )
    where a.PUNKTAS = 'Dvaro st.3';

Or alternatively, add a comparison clause:

update STOTMARS a
    set a.EILNR = (select coalesce(max(b.EILNR), 0)
                   from STOTMARS b
                   where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS
                  )
    where a.PUNKTAS = 'Dvaro st.3' and
          (select b.EILNR
           from STOTMARS b
           where a.PUNKTAS = 'Centro st.1' and a.MARS = b.MARS
          ) is not null;

Note that this does not use not exists, so it can take into account NULL value sin EILNR.

Upvotes: 0

Related Questions