RE: SQL Update More Than One Row

Michael,

Instead of trying to only get the first row try to retrieve only one value. If you're getting the same metro code from multiple records then do ( select distinct b.metro_code...). If you're getting different metro codes then you have to decide to take one of the values with a summary function like min(b.metro_code) or max(b.metro_code).

Paul

Principal Programmer Analyst
IS Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Thursday, October 14, 2010 11:39 AM
To: Midrange Systems Technical Discussion
Subject: SQL Update More Than One Row

This stuff makes me lose my mind...I want to update one table based on
another table, but I only want the first row (if multiple rows are
returned). Here's my SQL statement:

update exametp a set a.EMETRO = (select b.metro_code from geolocp b
where a.ezip = b.postal_code)
where exists (select b.metro_code
from geolocp b where a.ezip = b.postal_code)

How do I get the first row returned only? I've tried FETCH FIRST ROW ONLY,
but I'm getting a syntax error.

Thanks!