CODEWITHSUNDEEP
databasepostgresql
Martin
Martin

Reputation: 321

Issue with inner join when running query

I am attempting to run the following query in postgres:

update changeitem c inner join issuestatus s on s.id = c.oldvalue and c.field = 'status' set oldstring = s.pname;

I am getting the following error: 

ERROR:  syntax error at or near "inner"
LINE 1: update changeitem c inner join issuestatus s on s.id = c.old...

I am not completely sure what is wrong with my statement.

Upvotes: 0

Views: 40

Answers (5)

Michael Schaefers
Michael Schaefers

Reputation: 717

What you want to do can be performed with an UPDATE WITH clause:

update changeitem c 
set oldstring = s.pname
from issuestatus s
where  s.id = c.oldvalue and c.field = 'status';

see this sqlFiddle

Upvotes: 0

Server_bot
Server_bot

Reputation: 1

Correct query:

UPDATE c 
SET oldstring = s.pname
FROM changeitem c 
WHERE c.field = 'status' 
AND c.id in (SELECT s.id FROM issuestatus s WHERE s.id = c.oldvalue)

Upvotes: 0

Monty Khanna
Monty Khanna

Reputation: 1120

Try This : Not Tested :

update changeitem as c set oldstring = s.pname from issuestatus as s where s.id = c.oldvalue and c.field = 'status' ;

Upvotes: 0

Oleg Sapishchuk
Oleg Sapishchuk

Reputation: 657

Unfortunately UPDATE does not support explicit joins using the JOIN keyword. Check official documentation please.

Upvotes: 1

Christian
Christian

Reputation: 7320

Try this way:

update changeitem c 
set oldstring = s.pname
from issuestatus s 
where s.id = c.oldvalue 
and c.field = 'status';

Upvotes: 2

Related Questions