Select And Update within same query in MS Access & C#

Question

I would like to Select & Update the same record in access in same query from c# application. Something like-

int personId=command.ExecuteScalar(
  "Select Top 1 PersonId From Persons where IsLocked=false 
  AND UPDATE THE SELECTED ROW WITH IsLocked=true");

I must require the PersonId value which is being selected & updated in my code.

I saw some questions in SO, but those are not what I actually want.

Any help?

i can do the update and select in one statement like so ` Update AspNetRoles Set [Name] = 'test1' From AspNetRoles Where [Name] = 'test' ` but i think youd need a stored procedure to get the id back — Muckeypuck
– Muckeypuck, Commented Jan 28, 2016 at 5:36

mugabits · Accepted Answer · 2016-01-28 07:18:20Z

1

I'm not clear why you are only grabbing the personID and then want to change a different column, but to change only the first row returned then try:

UPDATE P
SET IsLocked = True
FROM Persons P
WHERE PersonId = (SELECT top 1 PersonId FROM Persons WHERE IsLocked = False ORDER BY PersonId)

The query will update the first PersonId with IsLocked = False (Assuming that person is not listed more than once in your table)

edited Jan 28, 2016 at 7:18

answered Jan 28, 2016 at 5:46

mugabits

1,0311 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gustav Over a year ago

The query will update some PersonId ... you (and the questioneer) miss an Order By PersonId Asc.

mugabits Over a year ago

Yes, you're right. I have fixed it. Thank you. Let's blame that one on late night coding.

s.k.paul Over a year ago

@Gustav, That is just a pseudo statement in order to explain my requirement.

HansUp · Accepted Answer · 2016-01-28 16:42:36Z

1

Access SQL does not allow you to both SELECT and UPDATE in a single query.

If you were doing this within an Access application session, you could use a custom VBA function which returns the PersonId and performs the UPDATE ... then call that function from a SELECT query. Unfortunately, you can't use a custom VBA function in a query run from outside an Access application session.

answered Jan 28, 2016 at 16:42

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

Select And Update within same query in MS Access & C#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related