CODEWITHSUNDEEP
sqloracle-database
that_guy_mcp
that_guy_mcp

Reputation: 25

Having Trouble Running An SQL Update Statement

Forgive me but I'm relatively new to SQL.

I am trying to update a column of a table I created with a function I created but when I run the Update Statement, nothing happens, I just see the underscore flashing (I'm assuming its trying to run it). The Update Statement is updating around 60,000 fields so I assume it should take a little while but it's been 10 minutes and no good.

I would just like to know if anyone knows just some general reasons that the underscore may be flashing. I know this is super general but I've just never seen this before.

Here's an image of what I'm talking about: https://i.sstatic.net/HpUT7.png

EDIT: There are exactly 67,662 records in the table. I've also just screenshotted the query and linked it.

Upvotes: 0

Views: 66

Answers (1)

Alex Poole
Alex Poole

Reputation: 191550

Your old-style joins have no join condition between the ap1/r1 pair and the ap2/r2 pair, so you're calling your calc_distance() function for 67,662 * 67,622 combinations of coordinates. The use of distinct is potentially a warning that you know you're getting duplicates. And then there is no correlation between the subquery and the update itself, so you're repeating that for each row in temproute. That will take a while.

It looks like you maybe don't want to be looking at the source airport from two copies of the route table; but the source and destination airports from a single copy.

Something like (untested):

UPDATE temproute tr
SET distance = (
  SELECT calc_distance(ap2.latitude, ap2.longitude, ap1.latitude, ap1.longitude)
  FROM routes r
  JOIN airports ap1 ON ap1.icaoairport = r.sourceid 
  JOIN airports ap2 ON ap2.icaoairport = r.destid
  WHERE r.routeid = tr.routeid 
);

If temproute is a copy of route too, which the line count implies, then you don't need to refer to route directly at all in the subquery, perhaps.

But I'm speculating about what you're doing.

Upvotes: 2

Related Questions