updating table using SELECT? 1

[xq]

i thought this query should be working, but when i double click that query, there is a warning msg says 'Operation must use an updateable query'.
that's the sql for that query, what's wrong with that?

UPDATE Acc, [CpyAccTbSun]
SET Acc.Suspend = (SELECT [CpyAccTbSun].Suspend
FROM [CpyAccTbSun], Acc
WHERE [CpyAccTbSun].[AccCode] = [Acc].[Number])
WHERE ((([Acc].[Number])=[CpyAccTbSun].[AccCode]));

thanks million if any one give me a clue!

 

[cmmrfrds]

Only put the table you are trying to update as part of the update statement, then tie the same table in the outer query to the correlated subquery with an Alias. There are other ways to do it but this should work.

UPDATE Acc as A
SET A.Suspend =
(SELECT [CpyAccTbSun].Suspend
FROM [CpyAccTbSun]
INNER JOIN Acc as B
ON [CpyAccTbSun].[AccCode] = B.[Number]
WHERE A.[Number] = B.[Number])

INNER JOIN CpyAccTbSun
ON A.[Number] = [CpyAccTbSun].[AccCode];

 

[xq]

thank u, but i have syntax error on INNER JOIN.

 

[xq]

I changed the code bit, no syntax error, but still got same warning msg: 'Operation must use an updateable query'.

UPDATE Acc as A

INNER JOIN CpyAccTbSun
ON A.[Number] = [CpyAccTbSun].[AccCode]

SET A.Suspend =
(SELECT [CpyAccTbSun].Suspend
FROM [CpyAccTbSun]
INNER JOIN Acc as B
ON [CpyAccTbSun].[AccCode] = B.[Number]
WHERE A.[Number] = B.[Number]);

Help~~~~~~~~~~~~~~~

 

[cmmrfrds]

Maybe the subquery is returning more than one value but in that case there should be a different error.

SET A.Suspend =
(SELECT TOP 1 [CpyAccTbSun].Suspend
FROM [CpyAccTbSun]
INNER JOIN Acc as B
ON [CpyAccTbSun].[AccCode] = B.[Number]
WHERE A.[Number] = B.[Number]);

Do you have write permissions to the MDB on the directory? How about security in the MDB, does your logon have write permissions.

 

[xq]

thank u, but first, i do have the write permisson, no proplem with that.
and i want to update few records at same time, that table B contains the record number which will be updated and new record value. table A is the table should be updated, do i have update table by each sql for each record?
by the way, still got same problem with changed sql.

 

[danvlas]

UPDATE Acc
SET Suspend = (SELECT CpyAccTbSun.Suspend
FROM CpyAccTbSun Inner Join Acc On CpyAccTbSun.AccCode = Acc.Number];

should work, provided the subquery returns ONLY ONE record.
The first Where is replaced by the Inner Join. The second Where disappears as it does nothing.

HTH,

Dan

 

[danvlas]

UPDATE Acc
SET Suspend = (SELECT CpyAccTbSun.Suspend
FROM CpyAccTbSun Inner Join Acc On CpyAccTbSun.AccCode = Acc.Number];

should work, provided the subquery returns ONLY ONE record.
The first Where is replaced by the Inner Join. The second Where disappears as it does nothing.

HTH,

Dan

 

[xq]

thanks, but i'm supposed to update all the record which is listed in table B, so acutally the sub query won't return only one result.

table A table B
ID value ID value
1 10 1 15
2 20 3 30
3 15 5 20
4 20
5 0

i want to update three records in table A which r listed in table B with new value in tables B as well.
what should i do?

 

[danvlas]

Try this query:

UPDATE acc INNER JOIN CpyAccTbSun ON acc.Number = CpyAccTbSun.AccCode SET acc.suspend = [CpyAccTbSun]![suspend];

I made two tables, ran it and worked fine...

Dan

 

[xq]

it does work! never thought it was so simple and easy, thanks million! and thank u all!