CODEWITHSUNDEEP
mysqlsqlsql-updatemysql-select-db
Santiago Bozzi
Santiago Bozzi

Reputation: 139

SQL UPDATE boolean from a db selecting a minium value of number WHERE boolean=false

I want to UPDATE my database from a previous selection. I mean I need to select the minium value of the register when the boolean=false and then change the value of the boolean of that record to true. They are all from the same table.

numberColumn    booleanColumn
     1                1                   
     2                1
     3                0
     4                0

Result need to be this:

numberColumn    booleanColumn
     1                1                   
     2                1
     3                1
     4                0

The statement im using is like this and there is probably an error on the syntax.

"UPDATE db(booleanColumn) SELECT MIN(numberColumn) FROM db WHERE boolean='0' SET boolean='1'"

Im feeling like I need to nest something like:

"UPDATE db(booleanColumn) 
SET boolean='1'
WHERE (SELECT MIN(numberColumn) WHERE boolean=0)"

but if not working also. I dont know if you are getting the question or I need to express myself better. Thank you from now,

Upvotes: 0

Views: 1685

Answers (2)

ghani319
ghani319

Reputation: 1

In SQL you can do this way

;with cte as  
(   
    select top(1) * from db
    where boolean = 0
   order by numberColumn
     )          
update cte set  boolean = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

In MySQL, you can do:

update db.table
    set boolean = 1
    where boolean = 0
    order by numberColumn
    limit 1;

Upvotes: 2

Related Questions