P Kishor wrote:
Gentlefolks,
I tried my own steps as stated below, and failed rather miserably. I
created everything as below, and ran an UPDATE yesterday mid-aft. Came
back this morning, and by mid-morning it hadn't even budged. Killed
the process, and tried a simple about 10 mins ago
SELECT * FROM v_attr WHERE pt_id = 1;
and even that hasn't returned anything as of yet.
By the way, I also added non-composite indexes to all the columns, and
ran ANALYZE before doing any of this.
Any suggestions, rays of hope, etc. welcome.
On 9/19/06, P Kishor <[EMAIL PROTECTED]> wrote:
I have the following two tables and related indexes --
CREATE TABLE IF NOT EXISTS pt (
id INTEGER PRIMARY KEY,
x REAL,
y REAL,
attr TEXT
)
CREATE INDEX ix_pt_x ON pt (x)
CREATE INDEX ix_pt_y ON pt (y)
CREATE TABLE IF NOT EXISTS py (
id INTEGER PRIMARY KEY,
xmin REAL,
ymin REAL,
xmax REAL,
ymax REAL,
attr TEXT
)
CREATE INDEX ix_py ON py (xmin, ymin, xmax, ymax)
I want to UPDATE pt SETting pt.attr = py.attr WHEREver the Count of
(pt.x BETWEEN py.xmin AND py.xmax AND pt.y BETWEEN py.ymin AND
py.ymax) is 1.
So, I have come up with the following way -- I have created a view to
do my SELECTs
CREATE VIEW v_attr AS
SELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS
py_attr
FROM pt JOIN py ON
(
(pt.x BETWEEN py.xmin AND py.xmax) AND
(pt.y BETWEEN py.ymin AND py.ymax)
)
GROUP BY pt_id_count
HAVING pt_id_count = 1
)
Well, any SELECTs from the view v_attr take forever. Anyway to speed
it up?
If all of the above works, then I can do the following UPDATE --
UPDATE pt AS a_pt
SET attr = (
SELECT py_attr
FROM v_attr
WHERE a_pt.id = v_attr.pt_id
)
And so I ask you, does this make sense? Is my JOIN okay? One local wag
was saying that JOINs can only be done using '=', not BETWEEN.
Nevertheless, perhaps a SQL guru on this list can guide me on this
task.
You have an error in the select statement inside your create view
statement.
sqlite> SELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS
py_attr
...> FROM pt JOIN py ON
...> (
...> (pt.x BETWEEN py.xmin AND py.xmax) AND
...> (pt.y BETWEEN py.ymin AND py.ymax)
...> )
...> GROUP BY pt_id_count
...> HAVING pt_id_count = 1;
SQL error: misuse of aggregate:
Sqlite does not complain when you create the view because it does not
compile the query at that point. You should get the error message if you
ever try to use the view.
The group by clause is wrong. You should change pt_count_id to pt_id.
Furthermore, standard SQL (I'm not sure about sqlite here) doesn't allow
you to use an alias name defined in the select clause in the having
clause, because those output fields may not exist yet. Since the having
clause will only return groups where the count is 1, there is no sense
returning that constant value from the view. Try this instead.
SELECT pt.id AS pt_id, py.attr AS py_attr
FROM pt JOIN py ON
(
(pt.x BETWEEN py.xmin AND py.xmax) AND
(pt.y BETWEEN py.ymin AND py.ymax)
)
GROUP BY pt.id
HAVING count(pt.id) = 1;
Also, our update statement will not work at all. Firstly, there is no AS
clause allowed in an update statement. Secondly, there is no condition
on the update so that all rows in the pt table will be changed to the
same new value of attr if it executes. The value would be the first
value returned by the subquery that is getting data from the view.
Given these errors, I don't think you tested the statements you posted.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
