UPDATE statement - Subquery returned more than 1 value.

[rzirpolo]

I'm having difficulty writing an UPDATE statement as I keep getting the error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

I can see the problem but I'm stuck on how I can resolve it and write the correct syntax. The subquery will return more than one row but I want the statement to update for each row populated. Can anybody help me write this query ?


update table1.incident

set table1.incident.map_reference =

(select table1.tmp_maprefchecking.mapref
from table1.tmp_maprefchecking, table1.tmp_errorlog, table1.incident
where table1.incident.incident_number = table1.tmp_maprefchecking.incid
and table1.tmp_maprefchecking.incid = table1.tmp_errorlog.cmcincidentnumber
and table1.tmp_errorlog.errorNumber = 6012
and table1.tmp_errorlog.failuredescription LIKE 'Error validating%'),

table1.incident.processed = 0

 

[rzirpolo]

It's ok I found out using an UPDATE FROM statement works. I knew I had come across this before..

update table1.incident

set table1.incident.map_reference = table1.tmp_maprefchecking.mapref,
table1.incident.processed = 0

from table1.tmp_maprefchecking, table1.incident
where table1.incident.incident_number
IN (select table1.tmp_maprefchecking.incid
from table1.tmp_maprefchecking, table1.tmp_errorlog
where table1.tmp_maprefchecking.incid = table1.tmp_errorlog.cmcincidentnumber
and table1.tmp_errorlog.errorNumber = 6012
and table1.tmp_errorlog.failuredescription LIKE 'Error validating the map_reference (""%')

 

[rzirpolo]

I jumped the gun... this statement runs but does not work as the data it has updated is all the same and invalid. Still require help.

 

[vongrunt]

Not so simple.... When subquery returns values A, B, C - which one you want to use for UPDATE?

In some cases SELECT TOP 1... ORDER BY is OK but that depends on data and relationships. Can you describe cardinalities (1:1, 1:n... ) between maprefchecking/incident and maprefchecking/errorlog?

In simplest case you probably have multiple entries in error log (same mapref values), so DISTINCT or TOP 1 in subquery should fix the problem.

 

[rzirpolo]

I managed to get it working using an UPDATE FROM statement, the reason the data being updated before was invalid was because I was missing a link between tables... the working statement is,

update table1.incident

set table1.incident.map_reference = table1.tmp_maprefchecking.mapref,
table1.incident.processed = 0

from table1.incident, table1.tmp_maprefchecking, table1.tmp_errorlog
where table1.incident.incident_number = table1.tmp_maprefchecking.incid
and table1.tmp_maprefchecking.incid = table1.tmp_errorlog.cmcincidentnumber
and table1.tmp_errorlog.errorNumber = 6012
and table1.tmp_errorlog.failuredescription LIKE 'Error validating the map_reference (""%'