Postgresql Syntax error at or near "where"

Question

I am getting the error

syntax error at or near "where" LINE 5: where zip in (select zipcode from zips where city = 'Sacra

when I try to run this code.

update listings
set price = CASE WHEN (listings.price IS NOT NULL) THEN (price * 
(((100+(select price_change from zips where zips.zipcode=listings.zip))/100)))
where zip in (select zipcode from zips where city = 'Sacramento');

Does anybody see any easy to fix errors? or did I come up with some garbage code?

You don't need the case, as you have no else part. Just add price is not null to your where clause and you can get rid of the whole CASE and make the update even more efficient. — user330315
– user330315, Commented Apr 7, 2015 at 7:05
just see the updated answer @a_horse_with_no_name suggestion is added :) — Vivek S.
– Vivek S., Commented Apr 7, 2015 at 7:24

Mike T · Accepted Answer · 2015-04-07 06:46:05Z

1

An SQL CASE expression needs to be ended with END keyword.

answered Apr 7, 2015 at 6:46

Mike T

44.4k18 gold badges166 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vivek S. · Accepted Answer · 2015-04-07 07:23:25Z

0

Add an end keyword just before where clause

update listings
set price = CASE WHEN (listings.price IS NOT NULL) THEN (price * 
(((100+(select price_change from zips where zips.zipcode=listings.zip))/100))) END
where zip in (select zipcode from zips where city = 'Sacramento');

As per @a_horse_with_no_name's Comment

update listings 
set price = (price * (((100+(select price_change from zips where zips.zipcode=listings.zip))/100))) 
where zip in (select zipcode from zips where city = 'Sacramento') and listings.price IS NOT NULL

edited Apr 7, 2015 at 7:23

answered Apr 7, 2015 at 6:46

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Comments

Almin Islamovic · Accepted Answer · 2015-04-07 06:45:22Z

0

update listings
set price = CASE WHEN (listings.price IS NOT NULL) THEN (price * 
(((100+(select price_change from zips where zips.zipcode=listings.zip))/100)))
where zip in (select zipcode from zips where city = 'Sacramento')

remove ; try again

answered Apr 7, 2015 at 6:45

Almin Islamovic

2781 gold badge5 silver badges13 bronze badges

Collectives™ on Stack Overflow

Postgresql Syntax error at or near "where"

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related