CODEWITHSUNDEEP
sqlpostgresql
QuickAccount123
QuickAccount123

Reputation: 189

SQL - Update all rows with certain condition

Hi i want to do is a query which do the following thing:

If i execute the query ,all rows will set to 0 except certain id in the where(which supposed to be always 1 and only one row can be active at the moment)

tbl_menu
id | serial
starter | varchar
plate | varchar
beverage | varchar
status | smallint

So if i have one registry with status in 1 and everything else in 0, when i execute this query, certain id i choose will change to 1 and the others to 0
also only one row status = 1

Upvotes: 0

Views: 8033

Answers (4)

Rahul Jain
Rahul Jain

Reputation: 1399

Try this:

UPDATE tbl_menu
set status = CASE WHEN id = 4 or status = 1 THEN 1 ELSE 0 END;

Upvotes: 1

mlinth
mlinth

Reputation: 3108

I think you have two choices with the design as it is. 1) Do it with two easy queries. 2) Write one more complicated query with a case statement.

I personally like easy:

UPDATE tblmenu SET status = 0 WHERE status = 1;
UPDATE tblmenu SET status = 1 WHERE id = n;

Although, having said that, I think a better approach is this...

  • Get rid of your status column
  • Create a new table called, say tblstatus with one column id
  • One record with the id of the record
  • Foreign key to your main table

Now all you have to do is:

UPDATE tblstatus SET id = n;

Faster, easier, more robust...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This doesn't solve the problem of the update, but it does solve the problem of enforcing that (at most) one row is active. Use a partial unique index:

create unique index unq_menu_active on tblmenu(flag) where flag = 1;

Upvotes: 0

schikkamksu
schikkamksu

Reputation: 85

This is a basic update statement. But if you could give more info on the column names and on what condition you want to do the updates it would be helpful. 

UPDATE tbl_menu SET (column) = value  
WHERE (condition)

Upvotes: 1

Related Questions