Having error while executing postgresqlquery

Question

I am facing error in my query while deleting row from two table which have same primary and foreign key : Query:

DELETE FROM TABLE1 INNER JOIN TABLE2 ON (TABLE1.id=TABLE2.id) WHERE TABLE1.id='21306';

ERROR: syntax error at or near "INNER"

Using rdbms POSTGRESQL

Unrelated to your problem, but: Postgres 9.3 and 9.5 are no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Nov 22, 2021 at 17:11
That syntax simply isn't correct. See the manual for the correct syntax. — user330315
– user330315, Commented Nov 22, 2021 at 17:12

Vikash Madhow · Accepted Answer · 2021-11-22 17:17:12Z

1

You can't have a join in the from clause of a delete in Postgresql (although this is supported in SQL Server). Any additional table that you want to participate in the delete must be added to the using clause. Try this instead:

DELETE FROM TABLE1
 USING TABLE2 
 WHERE (TABLE1.id=TABLE2.id) AND TABLE1.id='21306';

edited Nov 22, 2021 at 17:17

answered Nov 22, 2021 at 17:12

Vikash Madhow

1,34411 silver badges17 bronze badges

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

10 Comments

Dungeon wolf Over a year ago

I want to delete record from both the tables

Vikash Madhow Over a year ago

You can only delete from one table using one delete statement. You can delete from multiple tables in a single SQL statement using a WITH statement

user330315 Over a year ago

@Dungeonwolf: then you need to run two DELETE statements.

Dungeon wolf Over a year ago

is it possible to delete record from both table using a single query?

Dungeon wolf Over a year ago

can i get a query using with statement?

|

Collectives™ on Stack Overflow

Having error while executing postgresqlquery

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related