Compound update

Asked

Viewed 252 times

1

I have a table pessoas where, I have several fields referring to the person’s registration, One of these fields is the PES_NUMERO, which is nothing more than the code of the person in the system, well, I have another field called EST_NUMERO, where I inform what structure the person belongs to.

I need to update this EST_NUMERO according to a spreadsheet where I have the number plate.

Below the code I wrote so you can understand better 

USE RBACESSO_V100
GO
UPDATE PESSOAS 
SET EST_NUMERO = 100 -- Numero do código da estrutura que a pessoa deve receber.
WHERE PES_NUMERO = 1222244 -- Numero da matricula da pessoa a ser atualizada.

The command WHERE I inform the condition for the update, only that I need to enter several number plates there is not only one as I have about 2063 records to update, as I do to put a kind of condition there to pick up all the license plates, something like a and?

sql sql-update

  • One way would be this: WHERE PES_NUMERO IN ( A,B,C,D,E,F,G)

  • Maybe it helps if you put the tag the database you are using.

  • How will this be done? IE, how are you passing this to the database?

  • So until then I am manually informing in the database, or if I am already typing the license plate Cod manually

  • there is another way to bring this to the bank in a more practical way ??

  • Is the EST_NUMERO code from the spreadsheet? Or is it fixed?

  • Not this code is fixed the same is contained in another table in this same bank where it is made a specific registration of structure of the company !

Show 2 more comments

1 answer

2

A simple way: 

where pes_numero in ( a,b,c,d,e )

or

 where pes_numero in ( select matricula from tabela_matricula where 
     condicao )

  • Perfect, I think so will work, as I have no knowledge in the language was with incorrect syntax, I was not using the IN

  • Thank you very much !!!!!

  • Dear Reginaldo, your answer is valid yes, depending on the context. However, I noticed that Gustavo reported that there would be 2063 records and for IN clauses (at least in postgresql I’m sure) there is a limit of 1000 conditions if they are informed one by one (without an under-consultation). As the DBMS was not informed this reply may be valid or not.

  • That’s why I put a second option. With the second option works perfectly, regardless of the number of records.

  • So I have to pay attention to this amount of records, I will try to mount here with the second option of the Reginaldo, is to perform the tests

Browser other questions tagged sql sql-update

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