Sub query problem....bad day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Sub query problem....bad day

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-11-17 : 19:12:07

Can someone please help me with this subquery, it's driving my crazy. Here is what I have so far:

UPDATE subCategories sc SET sc.lastUpdatedOn = (SELECT l.lastUpdateOn FROM lastUpdatedDate l)
WHERE sc.subCategoryID=31;

I get the error 'Operation must use an updateable query' when I try to run the query. Both lastUpdatedOn fields are Date/Time data types.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-17 : 23:32:26

Hiya fellow cricketing legend (aren't we all)

What are you actually trying to do? Are you guaranteed that there is only one record in lastUpdatedDate ? (in which case why do the sub-select?)

or are you trying to do...

Update subCategories sc
Set sc.lastUpdatedOn = l.lastUpdateOn
from subCategories sc inner join lastUpdatedDate l
on sc.subCategoryID = l.subCategoryID
where sc.subCategoryID = 31

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-11-18 : 00:14:54

Yep, for sure, don't know what I was thinking.

And go the Aussie cricket team, I think we should have the poms stitched up.

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-11-18 : 00:40:51

Actually, I couldn't get that to work.

Basically the lastUpdatedDate table holds a single date value, and I want to update several records and set their lastUpdatedOn value equal to the single value in the lastUpdatedDate table.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-18 : 00:48:23

OK - still trying to make sure I understand...So there is one value only in lastUpdatedDate, or just one value to match for subCategoryID=31?

Which version of Access are you using? Some versions will complain unless you do [TABLENAME] in an update query.

So try

UPDATE [subCategories] sc
SET sc.lastUpdatedOn = (SELECT l.lastUpdateOn FROM [lastUpdatedDate] l)
WHERE sc.subCategoryID=31;

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-11-18 : 01:21:24

Yeah, there is only ever the one value in the table, and I just want to update each record that has a subCategoryID of 31.

When I run that I get the error I was getting before, 'Operation must use an updateable query'

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-18 : 01:57:10

If your table really only has one value then just do this:

UPDATE subCategories, lastUpdatedDate SET subCategories.lastUpdatedOn = [lastUpdatedDate]![lastUpdateOn]
WHERE (((subCategories.subCategoryID)=31));

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 11/18/2002 02:04:18

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-11-18 : 23:52:56

Thanks, that worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources