Incorrect syntax error "as" Postgres

Question

I am postgres here is my query

update cust_final_1 as cust set cust.enure_days=datediff(day,1date,current_date)
from  card as card join  cust_final_1 as cust on cust.cust_id=card.cardh_cust_id
left join bt_time as tim on card.cardh_act_dt=tim.time_id

on executing it gives error as incorrect syntax near "as"

What the mistake

This isn't Postgres syntax. datediff() is not a Postgres function. — Gordon Linoff
– Gordon Linoff, Commented Dec 2, 2014 at 12:30

Radu Gheorghiu · Accepted Answer · 2014-12-02 12:28:38Z

0

This should work for you:

UPDATE cust
SET cust.enure_days = datediff(day, 1 DATE, CURRENT_DATE)
FROM card AS card
INNER JOIN cust_final_1 AS cust
    ON cust.cust_id = card.cardh_cust_id
LEFT JOIN bt_time AS tim
    ON card.cardh_act_dt = tim.time_id

Just replace the table name with the alias, right after the UPDATE keyword

answered Dec 2, 2014 at 12:28

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

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

3 Comments

Hare Rama Hare Krishna Over a year ago

it gave this err ERROR: relation "cust" does not exist

Radu Gheorghiu Over a year ago

@HareRamaHareKrishna Did you test this code in your application or directly in the Postgres command line? I believe this isn't a problem with the SQL code, but with something in your application. (table references or something) - do a simple web search (this is just an example of what I found : Possible answer)

mlinth Over a year ago

@RaduGheorghiu I suspect he didn't change the table name from "cust" to "cust_final_1". Anyway - I don't think datediff is valid Postgresql syntax.

mlinth · Accepted Answer · 2014-12-02 12:49:34Z

0

Not sure what your columns mean, but here goes. I assume "1date" is the date you want the difference from

UPDATE cust_final_1 
SET enure_days=current_date - "1date" -- datediff is not postgresql, just subtract if they are both dates
FROM card 
left join bt_time as tim on card.cardh_act_dt=tim.time_id
WHERE cust_final_1.cust_id=card.cardh_cust_id

I'm also suspicious of the left join. Why do that? If there is no corresponding record in bt_time and "1date" is in that table, you're updating the record with NULL. I'd suggest using an INNER JOIN, which will just mean that missing records don't do anything.

answered Dec 2, 2014 at 12:49

mlinth

3,1586 gold badges35 silver badges32 bronze badges

Collectives™ on Stack Overflow

Incorrect syntax error "as" Postgres

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related