Is it possible to perform an Update in this way?

Question

Is it possible to perform an Update in this way?

Navigation

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

1

I have two tables, the Impressoras table is where the equipment data, such as model, assets, etc., is stored. And I have the Impressora Local table where the location where the printer is located is stored, the client where it is currently allocated.

My Impressoras table has a codigoempresa column where when the printer is registered the initial company is placed where it goes, and in that table Impressora Local a history is stored from where the printer passed, so in that table Impressora Local the company (company code) is always updated, but in the Impressoras table.

I was able to resolve this issue by keeping this data updated in the Impressora table also by creating a Funtion in the database that whenever a record is changed or added to the% updated automatically. This works normally.

But the first time, I will have to update this data in the arm, so I tried to do an Update command as follows:

UPDATE public.impressoras
    SET codigoempresa=(select codigoempresa from public.impressoralocal)
    WHERE public.impressoras.codigo = (select codigoimpressora from public.impressoralocal);

But the following error is returned to me:

ERROR:  more than one row returned by a subquery used as an expression
********** Error **********

ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000

My question is, is it possible to update this table Impressora Local , or will I be forced to update row by line

Note: This bank is a legacy for this reason I am having to adopt such a measure

Printers table:

LocaPrinterTable:

sql sql-update postgresql

asked by anonymous 10.07.2017 / 16:09

2 answers

0

Subqueries within your SET and WHERE return more than one value because you do not filter what you're specifically looking for.

Understand:

UPDATE public.impressoras
    SET codigoempresa=(select codigoempresa from public.impressoralocal WHERE public.nomeimpressora = 'Minha impressora')
    WHERE public.impressoras.codigo = (select codigoimpressora from public.impressoralocal WHERE public.nomeimpressora = 'Minha impressora');

10.07.2017 / 16:36

Send data from a GridView on a Form to ComboBox on another Form Objects in javascript

score 2 · Accepted Answer

Disregarding the fact that the codigoempresa column would not be required in the printer master, since you already have all the history in the other table:

For the multi-line problem, you can use LIMIT 1 or MAX([idHistorico])

For your update that will run the first time manually, you can do this: [Waiting for table structure to confirm fields]

Update impressoras set 
    codigoempresa = (
                     Select 
                         x.codigoempresa 
                     from impressoralocal x 
                     where x.codigoimpressora = impressoras.codigo 
                     order by x.codigo desc limit 1);

This code will update all records in impressoras