simple "update"

[Goalie3533]

I need to update the field on one of my tables with the data contained in a field on a different table. I tried doing this but I keep getting an error.

Any idea on how to do this?

Thanks.

-Goalie35

 

[lespaul]

what's the SQL you are using?

Leslie

 

[GingerR]

and what is the error?

Also, it's not good practice to store the same data in multiple places. What are you trying to accomplish?

 

[Goalie3533]

I'm using ms access to do my query.
My error says that it's "not an updateable query."


My query was:
"update products set cprice = (select tempPrice from tempTable) where catalogid > 1900"

Thanks again.

-Goalie35

 

[PHV]

If you have only one record in tempTable, you may try this:
UPDATE products, tempTable
SET products.cprice = tempTable.tempPrice
WHERE products.catalogid > 1900

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Goalie3533]

No, actually, I need to update 831 records, not just 1.

 

[PHV]

So, how do you know which tempPrice to choose for which cprice ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

When you get that error, I'm pretty sure there's the choice of hitting a HELP button with a suggestion of what to do. I think you have to first make a table of your filtered records (CatalogID>1900), then write an update query.

I also agree with PHV: where's your join?