Update problem 1

[jfokas]

I am trying to update some rows of a table with information in the same column from different rows. The statement I'm trying to use is:

update delivery_location_2
set courier_route = dl2.courier_route
from delivery_location_2 dl1, delivery_location_2 dl2
where dl1.group_id = dl2.id and dl1.entity_type = 'R'
and dl1.group_id is not null

this syntax has always worked for me in the past when I'm dealing with two different tables. But in working from the same table, I get a "ambiguous table name" error. I've tried a variety of variations to no avail. What is that obvious syntax that I'm overlooking?

Thanks,

John

 

[donutman]

Create a view of the table and do a join on the original table and the view. The ambiguity is gone!
-Karl

 

[donutman]

Thanks to someone elses post I discovered the "right" way to do what you want.

update dl1
set courier_route = dl2.courier_route
from delivery_location_2 dl1, delivery_location_2 dl2
where dl1.group_id = dl2.id and dl1.entity_type = 'R'
and dl1.group_id is not null

-Karl

 

[jfokas]

Thanks Karl,

It worked like a charm