Steve, Op 05-11-11 05:51, Steve Murphy schreef:
I give! I'm flummoxed! Here is what I have, 3 tables: schedule company building status0 3 x active 4 x active 5 x active 3 x active 3 x active 3 x active In the end, I want to replace the building id's above. They start out with the non-informative value of '1'; company id name status 3 x active 4 y active 5 z active building id company name 1 3 A active 2 3 B active 3 3 C active 4 4 D active 5 4 E active 6 4 F active 7 5 G active 8 5 H active 9 5 I active So, every company has 3 schedules. Of the 3, I'd like to select the *one* with the lowest id.
I think you mean every company has three buildings...
I'm using postgresql 8.1. Yes, ancient, but I have no choice in this affair. select schedule.id as sched_id, bld.id as bid from schedule left join company on schedule.company = company.id left join (select * from building where building.company = company.id order by id limit 1) as bld where schedule.status = 'active' and company.status = 'active' and bld.status = 'active'; I get a syntax error on the the "where". ERROR: syntax error at or near "where" at character …. LINE 6: where ^ If I leave out the where clause entirely, that's an error also, ERROR: syntax error at or near ";" at character …. LINE 5: … as bld ; ^ <it's right under the semicolon> So, it's expecting ***SOMETHING*** after the "as bld", but it sure isn't going to tell me what. What am I missing?
I think you actually want to do this: update schedule set building = (select id from building where company = schedule.company order by id limit 1); Best, Antonio. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
