Update Flag on Max Date (1 Viewer)

Freshman · Sep 21, 2019

Hi all,

I've seen the answer to my question on a few places but just can't seem to implement it on mine. Maybe I'm missing something when changing their fields to mine, so I'll give my table and field names if someone can help please

Table: Career
Fields:
CareerID AutoNum and PK
ID Num (relationship to main table not relevant now)
SinceDate Date in question
MrkC Flag

I need to set the MrkC flag to -1 for the Max 'SinceDate' of each 'ID' row

I keep getting a not-updatable query error

Thanks a lot

jdraw · Sep 21, 2019

Please post the SQL of your not-updatable query.
It would be helpful to readers if you would provide a short description of what you're trying to do. And a few samples of your desired output would help with context.

Freshman · Sep 21, 2019

Hi there, sorry I thought I was clear enough.
I have a table with multiple 'SinceDate' for each 'ID' row
So I need to flag the Max 'SinceDate' for each 'ID'

I attached a database file where I do that using no less than 3 queries and one temp table - madness

PS: Hold down shift on open

arnelgp · Sep 21, 2019

Code:

update Career inner join 
     (select [ID], max(SinceDate) as Expr1 group by [ID] from Career) AS T 
     on career.[ID] = T.[ID] and Career.[SinceDate]=T.[Expr1] 
set career.MrkC = -1;

Freshman · Sep 21, 2019

arnelgp said:

Code:

update Career inner join 
     (select [ID], max(SinceDate) as Expr1 group by [ID] from Career) AS T 
     on career.[ID] = T.[ID] and Career.[SinceDate]=T.[Expr1] 
set career.MrkC = -1;

Thanks for the speedy reply. Field is [ID] not [ID Num] sorry seems like I put the Data type to close to the field name.

Even when changing that I get the following error:

The SELECT steement included a reserved word or an argument name that is misspelled or misisng or the puntuation is incorrect

Here is the SQL I used:

update Career inner join
(select [ID], max(SinceDate) as Expr1 group by [ID] from Career) AS T
on Career.[ID] = T.[ID] and Career.[SinceDate]=T.[Expr1]
set Career.MrkC = -1

Gasman · Sep 21, 2019

Well it is hardly sensible using [ID] if the field is called [ID Num]?

arnelgp · Sep 21, 2019

create a query (query1)

Code:

SELECT career.id, Max(career.sincedate) AS Expr1
FROM career
GROUP BY career.id;

create the "final" update query adding query1 on the sql:

Code:

UPDATE Career SET Career.MrkC = -1
   WHERE career.careerid in  
   (select careerid from career t1 
   inner join query1 on t1.id = query1.id and t1.sincedate=query1.Expr1);

Freshman · Sep 21, 2019

arnelgp said:
create a query (query1)

Code:

SELECT career.id, Max(career.sincedate) AS Expr1 FROM career GROUP BY career.id;

create the "final" update query adding query1 on the sql:

Code:

UPDATE Career SET Career.MrkC = -1 WHERE career.careerid in (select careerid from career t1 inner join query1 on t1.id = query1.id and t1.sincedate=query1.Expr1);

Thanks for the above - is there no way to combine it in a single update query?
Else it will have to do and in anyway much better than what I had

arnelgp · Sep 21, 2019

I already tried on my first post, but no..

Gasman · Sep 21, 2019

Gasman said:
Well it is hardly sensible using [ID] if the field is called [ID Num]?

Well you changed your last post. but I owe you an apology. I misread the post as field being called [ID Num] as arnel had already used ID in his code?, so why mention it?

Getting late for me as well.

Freshman · Sep 21, 2019

Gasman said:
I owe you an apology

All good - Coding will do that to you

I'm off to bed now

Update Flag on Max Date (1 Viewer)

Freshman

Registered User.

jdraw

Super Moderator

Freshman

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Freshman

Registered User.

Gasman

Enthusiastic Amateur

arnelgp

..forever waiting... waiting for jellybean!

Freshman

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Gasman

Enthusiastic Amateur

Freshman

Registered User.

Similar threads

Users who are viewing this thread