Subscribe to this thread
Home - General / All posts - Update Query does not parse
Forest
596 post(s)
#24-Jul-19 03:24

The test update query is of the form:

UPDATE [A] SET [A].[PointID] = 1;

The table is local, and has a mfd_id column. The column being updated does not have an index that prevents duplicates.

I have no idea why it does not parse. A SELECT query using the same field names does parse

Any help much appreciated.

tjhb

8,958 post(s)
#24-Jul-19 04:23

What do you mean by "does not parse"? What error message do you get?

The query can be simplified to

UPDATE [A] SET [PointID] = 1;

but the redundant alias "[A]." does no harm. (I checked.)

Can you post a screenshot of the table schema?

tjhb

8,958 post(s)
#24-Jul-19 04:39

I get the impression (reading betwen the lines) that you have not posted the actual query that you are running. That would be best.

Forest
596 post(s)
#24-Jul-19 05:39

I will make an example test file and post it later. in the meantime, I went back to M8 which I hate doing but I don't have any drama there.

Dimitri


5,620 post(s)
#24-Jul-19 08:00

I went back to M8 which I hate doing but I don't have any drama there.

? 8 has way more "drama" than 9 when it comes to SQL. 9 is a far cleaner SQL. Learn 9 and use it and it will be much easier than 8 to get consistent, no surprises results.

But the way to learn 9 is to crank through the documentation for 9 and to work through the many SQL examples in that documentation. Trying to learn 9 by re-cycling 8 knowledge and only diving into the documentation when something doesn't work is a formula for unnecessary frustration.

If something doesn't work for you, launch a thread in the forum that provides full info exactly as you do it. SQL is talking to a computer, so details like typos can be a factor. The only way to find those is to paste the queries you are using into the thread exactly as you are using them.

I've had plenty of queries that don't work simply because I miscounted parentheses, forgot a matching [ or ] bracket, put a typo into a field name, forgot to change field names when copying and pasting snippets of SQL from one project into another, and so on Something as simple as WHERE [MyField] = 1 can be wrong if "MyField" is a text field and not a numeric field.

It also helps when learning to see what Manifold would write in a given situation, using the Edit Query button in the Transform or Select panes. It can also help to debug a query taking it step by step in the Command Window, copying and pasting parts of it to form a simpler query that you can then highlight and execute using ALT-ENTER.

KlausDE

6,356 post(s)
#23-Sep-19 14:26

I'm not sure if this is a similar issue:

An imported table with a btree Index field of type nvarchar needed addition of new records.

So I had to add the [mfd_id] field and a btree index based on this autoindent field.

Now there where two valid btree indexes.

This prevented every UPDATE query to set an other field not part of an index to work. Error message "Can't set value"

The UPDATE should use a field in another table INNER JOINed ON the first btree index (not the [mfd_id_x]) in both tabels. I.e. a simple 1 : 1 relation on an indexed field.

Why this?

The update did what it was supposed to when I deleted the field [mfd_id] and index.

adamw


8,775 post(s)
#23-Sep-19 16:31

I tried updating a table with MFD_ID_X and another btree index and I cannot see any issues:

--SQL9

 

CREATE TABLE t (

  mfd_id INT64INDEX mfd_id_x BTREE (mfd_id),

  i INT32,

  c NVARCHARINDEX c_x BTREE (c)

);

INSERT INTO t (i, c) VALUES (1, 'a'), (2, 'b'), (3, 'c');

 

UPDATE t SET i=i+1; -- works

 

UPDATE t SET i=5; -- also works

Did I misunderstand the problem?

KlausDE

6,356 post(s)
#24-Sep-19 13:01

It worked for me in another case, too.

Jet when I faced the error message "Can't set value" (not "Can't parse query") after tests to exclude NULLs, duplicates and invalide types ... it worked when I changed nothing but delete the second btree index. I'm sure that NULLs and duplicates for the index field didn't produce conflicts because in my case the update affected only a non-indexed field.

I know I was traped by this effect before and as a workaround replaced the direct UPDATE based on the JOIN of tables by a CREATE of a new table with INSERT based on the same JOIN. That instantly ran without error.

So it seems there are some mysterious conditions that affect an UPDATE only. I know that other than INSERT an UPDATE needs a btree index in the field list. Could it be that UPDATE requires all btree index fields of all tables even if only one of them (and not the one based on [mfd_id]) is used in the JOIN?

It would be helpfull to have a list of situations that through a specific error.

adamw


8,775 post(s)
#24-Sep-19 14:12

The error message you cite ("Can't set value") can be triggered by several different things in the case of an UPDATE, it is hard to theorize what specifically is the culprit in the query you have without looking at it. (I guess this is a sign that we should make error messages here and in other cases more descriptive - we'll try to do it.)

If you have a btree index on multiple fields and the table with that index participates in a JOIN with ON ...=... that only uses some of those fields, the resulting table might not expose a btree index on those fields depending on what happens in the join and what indexes the second table has.

KlausDE

6,356 post(s)
#24-Sep-19 22:48

Each field had it's own btree index in this case with no other field involved.

I guess I will face this situation again and hope it's in a situation that allows to prepare an example.

More descriptive error messages are welcome. On the other hand we'r used to error descriptions completeing help items for commands in other languages

KlausDE

6,356 post(s)
#25-Sep-19 11:11

I guess I see clearer now for JOIN results as base of an UPDATE query:

1. The source and the target table both need a unique index

-> otherwise you see error "Schema should contain at least one unique index."

AND

2. All field(s) part of the unique index in the target table need to be in list of fields in the UPDATE (SELECT <list of fields> FROM .. JOIN ..) SET ...

3. All field(s) part of the unique index in the source table need to be in list of fields

-> otherwise you see error "Schema should contain at least one unique index."

BUT

4. the source table MUST be filtered to allow a 1 : 1 relation (in case the source table has a unique index combining fields)

--> otherwise you see error "Cannot set value."

This last condition is new to mfd9. Mfd8 simply overwrites repeated matches.

adamw


8,775 post(s)
#25-Sep-19 11:42

Yes.

Let's say we have SELECT t.tid, u.uid, x, y, z FROM t INNER JOIN u ON t.tid=u.uid, and we try to determine if the result table of this SELECT will support UPDATE.

Without 1, either tid or uid are allowed to repeat, so the result table of SELECT can contain records with duplicate values of these fields, so you cannot update the result table using those fields as key - because when you want to say "please set X in this record to 5", you cannot use tid or uid to identify the record, they identify more than one record.

Without 2, eg, with tid and uid having unique indexes, but not being referenced in the join condition, the result table again can contain records with duplicate values of these fields. (The criteria here is more complex than "you have to use all fields in an index", you have to use enough fields for the join to be 1:1. Eg, if t has an index on region+country and u has an index on region, a join on t.region=u.region keeps things 1:1 and so the result table will stay writable even though t.country does not participate in the join condition.)

Without 3, the result table lacks data to identify records in the original tables.

I am not sure about "filtered" in 4, but yes, the relation has to be 1:1, 1:N/N:1/M:N all make the result table non-updatable because writes create ambiguities (this goes past just having record values potentially being written to multiple times with no good way to specify which of the writes should win - if you take record X in the result table and say "OK, I am going to allow writes to it even though this will change multiple records in the original tables" the effect of this write is that fine, you changed multiple records *and this changed* records in the result table that you didn't write to, this backfires in many optimizations).

KlausDE

6,356 post(s)
#25-Sep-19 12:51

I 'filtered' records by WHERE to get a 1:1 relation when the JOIN still allowed 1:N matches and the query ran.

adamw


8,775 post(s)
#25-Sep-19 15:51

Got it. If the join is INNER, WHERE is just an extension of the join condition. (Not so if the join is OUTER.)

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.