Re: sql statment

From: Joost Ouwerkerk <joost.ouwerkerk_at_sickkids.on.ca>
Date: Wed, 18 Nov 1998 19:23:14 GMT
Message-ID: <36531d3c.89180556@resunix.sickkids.on.ca>

An UPDATE statement goes a little differently from a SELECT statement, especially when more than one table is involved. You're getting an invalid column because you never identified your second table.

Assuming you want the lowest type value to appear for identical id numbers (you say the 'first' value -- what does that mean?):

Try this:

UPDATE table_one t1 SET type=

       (SELECT min(t2.type) FROM table_two t2
        WHERE t2.id=t1.id)

WHERE exists (SELECT 1 FROM table_two t2 WHERE t2.id=t1.id) ; Received on Wed Nov 18 1998 - 13:23:14 CST