Error in UPDATE:

  • Thread starter Thread starter Jamie Collins
  • Start date Start date
J

Jamie Collins

I can't get my UPDATE syntax to work when the SET and WHERE clauses
reference another table.

Here's a quick example:

CREATE TABLE Table1 (
MyKeyCol INTEGER NOT NULL PRIMARY KEY,
MyDataCol VARCHAR(10) NULL
)
;
CREATE TABLE Table2 (
MyKeyCol INTEGER NOT NULL PRIMARY KEY,
MyDataCol VARCHAR(10) NULL
)
;
INSERT INTO Table1 (MyKeyCol, MyDataCol)
VALUES (1, 'One')
;
INSERT INTO Table1 (MyKeyCol, MyDataCol)
VALUES (2, 'Two')
;
INSERT INTO Table1 (MyKeyCol, MyDataCol)
VALUES (3, 'Three')
;
INSERT INTO Table2 (MyKeyCol, MyDataCol)
VALUES (1, 'One')
;
INSERT INTO Table2 (MyKeyCol, MyDataCol)
VALUES (2, 'Amended')
;
INSERT INTO Table2 (MyKeyCol, MyDataCol)
VALUES (3, NULL)
;

The following query shows the rows in Table1 where the data in Table2
is different using the key column (in case it isn't obvious, I want to
write something that is portable and therefore avoid using Jet's
UPDATE..FROM syntax):

SELECT Table1.MyKeyCol, Table1.MyDataCol, (
SELECT Table2.MyDataCol FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol ) AS new_value
FROM Table1
WHERE EXISTS (
SELECT *
FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol
)
;

No problem there, but when I turn this into an UPDATE:

UPDATE Table1
SET Table1.MyDataCol = (
SELECT Table2.MyDataCol
FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol)
WHERE EXISTS (
SELECT *
FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol
)
;
I get the error, 'Operation must use an updateable query.'

Any suggestions?

Many thanks,
Jamie.

--
 
Jamie Collins said:
I can't get my UPDATE syntax to work when the SET and WHERE clauses
reference another table.

Here's a quick example:

CREATE TABLE Table1 (
MyKeyCol INTEGER NOT NULL PRIMARY KEY,
MyDataCol VARCHAR(10) NULL
)
;
CREATE TABLE Table2 (
MyKeyCol INTEGER NOT NULL PRIMARY KEY,
MyDataCol VARCHAR(10) NULL
)
;
INSERT INTO Table1 (MyKeyCol, MyDataCol)
VALUES (1, 'One')
;
INSERT INTO Table1 (MyKeyCol, MyDataCol)
VALUES (2, 'Two')
;
INSERT INTO Table1 (MyKeyCol, MyDataCol)
VALUES (3, 'Three')
;
INSERT INTO Table2 (MyKeyCol, MyDataCol)
VALUES (1, 'One')
;
INSERT INTO Table2 (MyKeyCol, MyDataCol)
VALUES (2, 'Amended')
;
INSERT INTO Table2 (MyKeyCol, MyDataCol)
VALUES (3, NULL)
;

The following query shows the rows in Table1 where the data in Table2
is different using the key column (in case it isn't obvious, I want to
write something that is portable and therefore avoid using Jet's
UPDATE..FROM syntax):

SELECT Table1.MyKeyCol, Table1.MyDataCol, (
SELECT Table2.MyDataCol FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol ) AS new_value
FROM Table1
WHERE EXISTS (
SELECT *
FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol
)
;

No problem there, but when I turn this into an UPDATE:

UPDATE Table1
SET Table1.MyDataCol = (
SELECT Table2.MyDataCol
FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol)
WHERE EXISTS (
SELECT *
FROM Table2
WHERE
(Table1.MyDataCol<>Table2.MyDataCol
OR (Table1.MyDataCol IS NULL AND NOT Table2.MyDataCol IS NULL)
OR (Table2.MyDataCol IS NULL AND NOT Table1.MyDataCol IS NULL))
AND Table1.MyKeyCol=Table2.MyKeyCol
)
;
I get the error, 'Operation must use an updateable query.'

Any suggestions?

Many thanks,
Jamie.

--
Click to expand...

This is a complete PITA in Access. If any underlying query or subquery is
not updatable (even if it's only used in the criteria) then this is what
happens. I sometimes resort to running a make-table query, and then doing
the update from the data in the resulting table.
 
Brian said:
This is a complete PITA in Access. If any underlying query or subquery is
not updatable (even if it's only used in the criteria) then this is what
happens. I sometimes resort to running a make-table query, and then doing
the update from the data in the resulting table.
Click to expand...

Thanks, Brian.

Here's a much simpler query that fails in exactly the same way:

UPDATE Table1
SET MyDataCol = (SELECT MyCol FROM SingleRowTable)

Now why is Jet concerned about whether the 'query' part is updatable?
I'm not changing values in the query!

Jamie.

--
 
Jamie Collins said:
Thanks, Brian.

Here's a much simpler query that fails in exactly the same way:

UPDATE Table1
SET MyDataCol = (SELECT MyCol FROM SingleRowTable)

Now why is Jet concerned about whether the 'query' part is updatable?
I'm not changing values in the query!

Jamie.

--
Click to expand...

I know! As I said, it's a PITA!
 
Brian said:
I know! As I said, it's a PITA!
Click to expand...

And you also said, 'I sometimes resort to running a make-table query,
and then doing the update from the data in the resulting table.' Can
you please show me how you would do this, using my exmaple (or another
of your chosing)?

Thanks again,
Jamie.

--
 
Back
Top