How to set only one row of the column in which all values in this column are equal?

Asked

Viewed 75 times

1

I’m trying to set the column ocupado just one line at a time, something like: 

update suite set ocupado = 'S' where tipo = 'i';

But this instruction changes all rows in the column ocupado where tipo is i, and that’s not what I want. I want only one line between these to be changed to "S", the others that meet the condition should remain unchanged.

It is a reservation system, no matter which line is changed, but it should be only one that meets the above conditions. In the next run of this query it will do with another line because this ceases to meet the condition.

+--+-----+--------+
|id|tipo |ocupado |
+-----------------+
|01| i   | N      | <- alteraria só esta 
|02| i   | N      |
|03| c   | N      |
|04| c   | N      |
+-----------------+
mysql sql sql-update
  • 1

    need to explain better, use more correct terms, who knows to give some example of what happens. The described form does not make sense. And the code should do what it seems to want, but it says it doesn’t. Field and column are the same thing. Elements? I don’t know what this means in a database, mainly in a column, would it be rows? You want it in just one row ocupado change to S? What would that line be?

  • That’s right! I want only one row of the busy column to change to S.

  • 1

    Which line?....

  • Any one, but only one at each execution of the sql command. To explain better, this would be a hotel that at each suite reservation the status of the suite changes to: "Occupied suite? Yes", Occupied suite? No.

  • 1

    @Jfé put a LIMIT 1 at the end of the query then.

  • I’ve already done this, but when it runs again the status does not change because the limit is 1.

  • 2

    Use the ID guy, he’s unique

Show 2 more comments

1 answer

0


Putting faith in what he explained in the comments would be this:

update suite set ocupado = 'S' where tipo = 'i' and ocupado <> 'S' limit 1;

I put in the Github for future reference.

So you make sure you do it in one line at a time, always if the type is selected and you don’t do it where you already have one N.

  • Very grateful to be helping me! It did not serve because in this case there are only two values (status) that are N and S, when the value of ocupado is not S soon he will be N, then this command will not change the value of ocupado for S because it has only two values to ocupado.

  • Yeah, I forgot to change

  • It worked!! I can’t thank you enough...

Browser other questions tagged mysql sql sql-update

You are not signed in. Login or sign up in order to post.