Pin/Flag a record in a mysql table to true and the rest to false in a single statement

Question

Address Table with 50 records. Has a bool field called "primary_address_indicator". I want to update a selected record to true and automatically set all other 49 records to false.

I know I can do this with 2 sql statements First (update all records to false)

UPDATE address SET address.primary_address_indicator=0

Then (update specific record to true)

UPDATE address SET address.primary_address_indicator=1 WHERE address.record_id=31

Is there a way to do this in a single sql statement? Something like @Rank? Keeping it simple... no Unions or some weird self table join. Maybe what I am looking for does not exist...and that is fine too. just curious.

can you show some sample data? also mention how a record should be selected for update. — Vamsi Prabhala, Dec 14 '16 at 00:05
Possible duplicate of [MySQL update case help](http://stackoverflow.com/questions/6734231/mysql-update-case-help) — Sean McSomething, Dec 14 '16 at 00:06

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

Update with Case

  UPDATE tableName 
         SET Pin = CASE
                   WHEN 1=1      --'your condition' 
                       THEN True
                   ELSE False
                   END

PC : @keWalker

edited May 23 '17 at 12:30

Community

1
1

answered Dec 14 '16 at 00:37

monikapatelIT

977
14
26

This sql statement looks expensive. Seems that the 2 simple updates statements would have less overhead. But yes, I guess this does the trick, thanks. Will read more about CASE inside queries – Lixo Dec 14 '16 at 00:42

score 0 · Answer 2 · answered Dec 14 '16 at 01:21

0

The simplest way is simply to treat the boolean result as a number:

UPDATE address a
    SET a.primary_address_indicator = (a.record_id = 31);

CASE is the formal way to do it. This short-hand is specific to MySQL but it is one of the database-specific features that I like.

answered Dec 14 '16 at 01:21

Gordon Linoff

1,242,037
58
646
786

So what you are doing is a single update statement that sets all records to the selected record we want to pin. nice. But now I can not use that column in a sort, to have record 31 as the first record in my displayed table – Lixo Dec 14 '16 at 03:02
@Lixo . . . What is a "displayed table"? If you have a `select` statement, then you need an `order by` to specify the ordering of the result set. – Gordon Linoff Dec 14 '16 at 03:04
I sort by primary_address_indicator asc, and then by record_seq_num and then I display the result in a html table. So that the pin record is always displayed first regardless of how the other columns are sorted and then. Thanks, I am good now, I see several ways of getting what I need done. :) – Lixo Dec 14 '16 at 03:51

Pin/Flag a record in a mysql table to true and the rest to false in a single statement

2 Answers2