Update with SET from another table

Question

Update with SET from another table

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I'm trying to do an update with JOIN ...

The following statement was just a kick, to get the idea of what I want to do:

UPDATE PCPSEQPROC_001  PCPSEQPROC 
SET 
    PCPSEQPROC.ID_PCPSEQPROC = PCPSEQ.ID_PCPSEQ
    INNER JOIN PCPSEQ_001 PCPSEQ ON PCPSEQ.PRODUTO = PCPSEQPROC.PRODUTO
WHERE PCPSEQPROC.ID_PCPSEQPROC IS NULL

Edit

Query must be performed in Firebird.

sql-update firebird

asked by anonymous 29.03.2018 / 21:40

2 answers

How to calculate monthly Cvar in R? Json - JAVA (Eclipse)

score 1 · Answer 1

Try this:

UPDATE PCPSEQPROC_001
SET 
    PCPSEQPROC.ID_PCPSEQPROC = PCPSEQ.ID_PCPSEQ
FROM PCPSEQPROC_001 AS PCPSEQPROC
    INNER JOIN PCPSEQ_001 PCPSEQ ON PCPSEQ.PRODUTO = PCPSEQPROC.PRODUTO
WHERE PCPSEQPROC.ID_PCPSEQPROC IS NULL

Edit Firebird:

UPDATE PCPSEQPROC_001
SET 
    PCPSEQPROC.ID_PCPSEQPROC = (SELECT PCPSEQ.ID_PCPSEQ FROM PCPSEQPROC_001 AS PCPSEQPROC
    INNER JOIN PCPSEQ_001 PCPSEQ ON PCPSEQ.PRODUTO = PCPSEQPROC.PRODUTO
WHERE PCPSEQPROC.ID_PCPSEQPROC IS NULL)

score 1 · Answer 2

Try this way it's an update with select correlated (correlated select) ... something like this:

UPDATE PCPSEQPROC_001  PCPSEQPROC 
SET 
   PCPSEQPROC.ID_PCPSEQPROC = (select PCPSEQ.ID_PCPSEQ from
       PCPSEQ_001 PCPSEQ where PCPSEQ.PRODUTO = PCPSEQPROC.PRODUTO)
WHERE PCPSEQPROC.ID_PCPSEQPROC IS NULL

If you are ambigous in the subselect, you have more than one key, you are not one for a PCPSEQPROC relationship and PCPSEQ_001, in this case, you would need something like the following to retrieve a single line in the subselect, but be sure This is what you want !!!

UPDATE PCPSEQPROC_001  PCPSEQPROC 
SET 
   PCPSEQPROC.ID_PCPSEQPROC = (select MIN(PCPSEQ.ID_PCPSEQ) from
       PCPSEQ_001 PCPSEQ where PCPSEQ.PRODUTO = PCPSEQPROC.PRODUTO)
WHERE PCPSEQPROC.ID_PCPSEQPROC IS NULL

I do not have a seat to test but that's about it!