Error -What am i doing wrong in this sql query?

Question

I am trying to update values in a table from the same table using a particular condition but it is not running

I have tried it using "WITH"

update my_table as res1
set res1.my_column = (select res2.my_column from my_table as res2
                      where res2.parent_id is null and res2.is_company = true)
where res1.parent_id=res2.id;

Shawn.X · Accepted Answer · 2019-06-11T08:27:33.063

0

Just try SQL below:

update my_table as res1 set my_column = res2.my_column from my_table res2 where res1.parent_id = res2.id and res2.partner_id is null and res2.is_company;

edited Jun 11 '19 at 08:27

answered Jun 11 '19 at 08:17

Shawn.X

1,323
6
15

hi.. thnx for ur answer....i tried it using your sql query.. it is returning the same error that 'missing FROM-clause entry for table "res2" ' i was getting from my query – Nick Dhiman Jun 11 '19 at 08:25

score 0 · Answer 2 · answered Jun 11 '19 at 08:17

0

You don't specify the type of SQL server, but generally an UPDATE from a SELECT takes a particular syntax:

How do I UPDATE from a SELECT in SQL Server?

answered Jun 11 '19 at 08:17

thnx for replying & sorry i forgot to mention.. the server is postgresql – Nick Dhiman Jun 11 '19 at 08:23

Error -What am i doing wrong in this sql query?

2 Answers2