Jet SQL query med join subselect giver non updateable query fejl

You could try making a query in place of the sub select then make a join on familia.id

Thanks for responding, Terry.

As far as I know, I can't make joins in an UPDATE, but perhaps I don't understand what you mean. Could you give me an example?

can you send an Access db with the tables in?

ekspertenATsanthell.dk
AT = @

Are you executing the SQL on a web server?

Thanks, Terry, I've sent you the database.

The SQL is being executed on a windows machine in a C#.Net application, i.e. not a web server.

Let me know if you need any more info.

qryMarcaProducto:
SELECT marca.suprabrandid, producto.idfamilia
FROM producto INNER JOIN marca ON producto.idmarca = marca.id
WHERE (((producto.active)=1 Or (producto.active)=-1));


SQL:
UPDATE familia INNER JOIN qryMarcaProducto ON familia.id = qryMarcaProducto.idfamilia SET familia.suprabrandid = [marca].[suprabrandid];


Silly that this is necessary, why shouldnt a subselect work?

It would (Maybe (to cover myself :o))) also be possible to do it  all in one SQL by having the SQL in qryMarcaProducto return a derived table (TEMP table) and joining on this in the UPDATE SQL.

Ok, Terry, your suggestion worked, but since I had to create it all through SQL I'm just going to post the final solution:

CREATE VIEW qryMarcaProducto (suprabrandid, idfamilia) as SELECT marca.suprabrandid, producto.idfamilia FROM producto INNER JOIN marca ON producto.idmarca = marca.id WHERE (((producto.active)=1 Or (producto.active)=-1));

UPDATE familia INNER JOIN qryMarcaProducto ON familia.id = qryMarcaProducto.idfamilia SET familia.suprabrandid = qryMarcaProducto.suprabrandid;

Thank you very much for helping out on this.

Eksperten har aabenbart aendret sig lidt siden jeg sidst har brugt sitet. Hvordan giver jeg dig point?

glad to hear you got it working.

It seems that quite a few users have problems with giving points.
You should be able to select from a list of those who have placed an answer whichI have  d. 15. januar 2010 kl. 11:28:22

Ok, it was available now, don't know what happened earlier.
Thanks again for the help :)

thanks