UPDATE statement too complex

[somethingsimil]

The following update statement is throwing the error "SQL expression is too complex"

UPDATE "C:\scratch\INS.DBF"
SET c = (
SELECT tu.newc
FROM "C:\scratch\TOUPDATE.DBF" as tu
WHERE tu.rec = INS.rec
)
WHERE rec in (select rec from "C:\scratch\TOUPDATE.DBF")


I also tried with the last WHERE clause being "WHERE EXISTS...", but I get the same error.

What am I missing? The TOUPDATE.DBF table is only a few hundred rows - although the INS table is about 34000 rows.

[Admin]

Most likely the nested query

Code: Select all

SELECT tu.newc
FROM "C:\scratch\TOUPDATE.DBF" as tu
WHERE tu.rec = INS.rec

returns more than one record, this is the problem. Try to use some group function (e.g. MAX() or DISTINCT and GROUP BY).

[somethingsimil]

Hi Oleg, thanks for the reply...

No,

select rec, count(*)
from "C:\Users\administrator.ATDASSURANCES\Desktop\scratch\TOUPDATE.DBF"
group by rec
having count(*) > 1

returns zero rows

Same with the INS.DBF table.

I created a work around by pre-marking the rows in INS.DBF that needed to be updated with a separate update statement so I could use a simple where col_x=xx instead of the WHERE rec IN (select...) clause, and it worked fine.

Is there a better way - or is this a limitation with the SQL driver in Windows or something?

[Admin]

Either here we have a logical mistake (e.g. trying to update one row with several values from nested query), or some limitations in OLE DB driver. To make an investigation, please send me these DBF files to support email (if you wish).