CODEWITHSUNDEEP
mysqlsql
Steffen Dahlin
Steffen Dahlin

Reputation: 1

Mysql set boolean to 1 when requirements are met all others with requirements to 0

I have an image table in a mysql database with the following fields

id         int primary key 
type_id    int
product_id int
source     varchar
created    timestamp
expiry     timestamp
active     tinyint

What I need to do is set 1 picture active ( active=1 ) where the product_id, source and type_id is the same, the one that should be set active should be the one with the newest created timestamp and if I have 2 with the same timestamp I need the one with the highest id set active AND expiry should be > CURRENT_DATE, all others with the same product_id, source and type_id should have active set to 0. This should be done for all where product_id, source and type_id are the same.

Can this be done in 1 SQL?

Upvotes: 0

Views: 55

Answers (1)

forpas
forpas

Reputation: 164099

For MySql 8.0+ you can use ROW_NUMBER() to return the rows where active must be updated to 1 and LEFT join to the table in the UPDATE statement:

update tablename t left join (
  select t.* 
  from (
    select *,
      row_number() over (partition by product_id, source, type_id order by created desc, id desc) rn
    from tablename
    where (product_id = source) and (product_id = type_id) and (expiry > currrent_date)
  ) t
  where t.rn = 1
) tt on tt.id = t.id
set t.active = (tt.id is not null)

Upvotes: 1

Related Questions