Update different rows.

[ennrike]

Hi,

How can I update multiple rows using a single SQL command, one of the columns involved is the primary key (User):

Example:

UPDATE MyTable SET Status = true WHERE User = 'John'

UPDATE MyTable SET Status = false WHERE User = 'Peter'

How can I do this with one SQL command.

Thanks, regards

 

[boblarson]

UPDATE MyTable SET Status = false WHERE User In ('Peter', 'John')

 

[Brianwarnock]

Bob she wanted it True for John. Not sure if they are the only 2 values, kinda doubt it.

Brian

 

[boblarson]

Bob she wanted it True for John. Not sure if they are the only 2 values, kinda doubt it.

Brian

Ah missed that part. Yes, it would be more trouble than it is worth to do that.

 

[Brianwarnock]

Just realised that I forgot to suggest using IIf

Status=IIf([user] etc

the exact layout depending on whether John and Peter only users or not.

Brian

 

[ennrike]

Sorry, I am novice,
I would need different values for Status for each User, how can I use the "IF" inside the SQL command?

 

[Brianwarnock]

Assuming only the 2 users mentioned

UPDATE MyTable SET Status = IIf([user] = "John",True,IIf([user]="Peter",False))

If there are more then a different approach would be better, but more info would be needed.

Brian

 

[vbaInet]

Or a table with the User's name and the Update Value. Use that as the source of your Update query.

 

[gemma-the-husky]

something like this, with 2 functions

UPDATE MyTable SET Status = " & ResultOfBOOLEANFunction & " WHERE UpdateThisUserTestFunction([userid]) = " & true

I tend to use query design pane, but I think the SQL looks like this.

 

[Brianwarnock]

Or a table with the User's name and the Update Value. Use that as the source of your Update query.

I was thinking along the lines of a table with the status for each user instead of carrying it in the "main" table, but really we need to know more about the DB.

Brian

 

[vbaInet]

I was thinking along the lines of a table with the status for each user instead of carrying it in the "main" table, but really we need to know more about the DB.

Brian

We sure do Brian. I was actually referring to a separate table with just those fields, just like you were thinking. Obviously a viable solution if there are a good number of user names.

 

[Brianwarnock]

Yes with the separate table each user only appearing once then an update query is not requied, just use a form based on the table to make the changes.

Brian

 

[vbaInet]

Yes with the separate table each user only appearing once then an update query is not requied, just use a form based on the table to make the changes.

Brian

Absolument!