CODEWITHSUNDEEP
sqlinformix
user1600801
user1600801

Reputation: 311

284: A subquery has returned not exactly one row

I'm trying to do an update massive to a table in INFORMIX, but my query returns this error:

284: A subquery has returned not exactly one row

This is my query:

update newLocations set
       description=
             (select unique b.description from newLocations a,locations b
             where a.id_location=b.id_location )

Here are mi locations table

Table: locations        
id_location id2_location    description
02          AAA00           AS-LOC1 
05          AA000           AS-LOC2
10          AA010           AS-LOC7
20          AA020           AS-LOC8
30          AA030           AS-LOC9
40          AA040           AS-LOCA
50          AA050           AS-LOCB

Here are mi newLocations table

Table: newLocations     
id_location description
02          
05          
05          
05          
05          
05          
10          
20          
30          
40          
50

My subquery returns:

AS-LOC1 
AS-LOC2
AS-LOC7
AS-LOC8
AS-LOC9
AS-LOCA
AS-LOCB

How can I do to assign the description in newLocations, relating id_location from locations?

This is the solution, thanks to Joseph B

update newLocations 
  set description=
       (select max(l.description)
       from locations l
       where newLocations.id_location=l.id_location)
  where exists
  (select 1
   from locations l2
   where newLocations.id_location=l2.id_location);

And this error:

 201: A syntax error has occurred.

Upvotes: 1

Views: 8130

Answers (2)

famedoro
famedoro

Reputation: 1241

Here is another solution.

update newLocations nl
set description=
         (select l.description) 
          from locations l
          where nl.id_location=l.id_location l.rowid in 
(select min(l2.rowid) locations l2 where nl.id_location=l2.id_location));

Upvotes: -1

Joseph B
Joseph B

Reputation: 5669

Try this:

update newLocations nl
set description=
             (select MAX(l.description) 
              from locations l
              where nl.id_location=l.id_location )
where exists
(select 1 
from locations l2
where nl.id_location=l2.id_location);

Here's a SQL Fiddle using PostgreSQL.

Upvotes: 4

Related Questions