Re: What is *wrong* with this query???

From: Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
To: Steve Murphy <smurphy(at)intorrent(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: What is *wrong* with this query???
Date: 2011-11-05 21:37:24
Message-ID: 4EB5AC94.2070302@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Cstdenis 2011-11-05 21:48:34 Re: Distinct on a non-sort column
Previous Message John R Pierce 2011-11-05 19:58:22 Re: Distinct on a non-sort column