Update with the SET of another table

Asked

Viewed 2,109 times

2

I’m trying to update with JOIN...

The following instruction 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

Consultation should be performed in Firebird.

firebird sql-update

  • In which database? Mysql? Postgres? MSSQL?

  • If Mysql syntax is incorrect, the SET comes after the INNER JOIN

  • 2

    gbd is the Firebird

2 answers

1

Try this way:

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)

  • No select to have "from", error!

  • You had not made your bank explicit when I answered. See this link and see if it helps you. https://social.msdn.microsoft.com/Forums/pt-BR/9304439c-b413-4c1f-ae1a-e282439b8c5a/update-no-firebird-com-mais-de-uma-tabela-como-fazer?forum=vscsharppt&forum=vscsharppt

  • with sub-select, from ambiguous Multiple Rows,

  • Does your select return only one record or more? It should return only one

1

Try this way is an update with select correlated ( Correlated select )... something like that:

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 give ambigous in the subselect, you have more than one key, it is not one to one the relationship of PCPSEQPROC and PCPSEQ_001, in this case, You would need something like the following to recover a single line in the subselect, but be sure that 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 don’t have a test bench but that’s about it!

Browser other questions tagged firebird sql-update

You are not signed in. Login or sign up in order to post.