[SOLVED] change one field of one row to one value, every other row a different value

[jordanwb]

I have a MySQL table like so:

 

CREATE TABLE IF NOT EXISTS `paul_pages` (
  `page_id` int(10) unsigned NOT NULL auto_increment,
  `page_title` varchar(128) NOT NULL,
  `page_content` text NOT NULL,
  `page_use_meta` tinyint(1) NOT NULL default '0',
  `page_default` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`page_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

 

What I want to do is if page_id of the row is equal to $page_id, I'll change page_default to 1, but for every other row change it to 0. Can that be done in one query or do I have to do it with 2 queries?

[jordanwb]

Basically what I want to do is an IF ELSE statement in the query.

[revraz]

So you want to use PHP to cycle through the table and check table.page_id = $page_id and if so Update page_default = 1 else page_default = 0?

[akitchin]

the mysql manual is your friend:

 

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

 

it might be faster for you to simply run two queries: in the first, set all page_defaults to 0, then update only the row with the given page_id to have page_default of 1.

[jordanwb]

So you want to use PHP to cycle through the table and check table.page_id = $page_id and if so Update page_default = 1 else page_default = 0?

 

No. This is sort of what I want to do in the query:

 

UPDATE `paul_pages`
IF `page_id` = 3 THEN SET `page_default`=1
ELSE SET `page_default`=0
END IF

[Mchl]

I suppose you have only one row with default = 1 so this might work

 

UPDATE `paul_pages` SET `page_default` = 1 - `page_default` WHERE `page_id` IN ($oldDefaultID, $newDefaultID)

 

you need to know $oldDefaultID though

[Barand]

UPDATE `paul_pages`

SET `page_default`= IF(`page_id` = 3, 1, 0)

 

[jordanwb]

Thanks Barand that works.