Hi Guys!
I have read on some forums that its possible to update multiple rows using one query.
I would like to do something like the following, but it doesn’t work. Any suggestions welcome.
update settings set case when array_key = 'email_type' then value='SMTP'
You don’t need the when … then … construction here.
If I understand correctly what you want, you should use:
UPDATE
settings
SET
value='SMTP'
WHERE
array_key'email_type'
Sorry this is not what I meant. I seem a bit unclear in my original post, but what I want to do is the following:
update settings set case
when array_key='email_type' then value='SMTP'
when array_key = 'email_host' then value='server.gmail.com'
update settings
set value = case array_key
when 'email_type' then 'SMTP'
when 'email_host' then 'server.gmail.com' end
where array_key in ('email_type','email_host')
Ok great, I am running the following query, but it doesn’t update any rows:
update settings set value =
case array_key
when 'email_type' then 'SMTP'
when 'email_host' then ''
when 'email_port' then ''
when 'email_username' then ''
when 'email_password' then '' end
where array_key in ('email_type','email_host','email_port','email_username','email_password')
Here is my current table dump:
CREATE TABLE `settings` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`array_key` varchar(100) NOT NULL,
`value` varchar(100) DEFAULT NULL,
`category` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `settings`
--
INSERT INTO `settings` VALUES(1, 'admin_session_name', 'jbs_admin', 'general');
INSERT INTO `settings` VALUES(2, 'language', '/includes/languages/en/default.php', 'general');
INSERT INTO `settings` VALUES(3, 'admin_cookie_name', 'jbs_admin_cookie', 'general');
INSERT INTO `settings` VALUES(4, 'max_featured_advertisers', '5', '');
INSERT INTO `settings` VALUES(5, 'max_job_of_week', '2', '');
INSERT INTO `settings` VALUES(6, 'email_type', 'SMTP', 'email');
INSERT INTO `settings` VALUES(7, 'smtp_host', NULL, 'email');
INSERT INTO `settings` VALUES(8, 'smtp_port', NULL, 'email');
INSERT INTO `settings` VALUES(9, 'smtp_username', NULL, 'email');
INSERT INTO `settings` VALUES(10, 'smtp_password', NULL, 'email');
email_type already has the value you want to set, and the others don’t exist in your table data.
because there is only one row that satisfied the WHERE clause
it attempts to set the value for that row to SMTP, but that’s the value it has already, so it doesn’t bother updating it
darn, guido you’re too fast sometimes…
Thank you 
Ok guys, just tried this, but it still doesn’t update:
update settings set value =
case array_key
when 'email_type' then 'SMTP'
when 'email_host' then 'test'
when 'email_port' then 'tes'
when 'email_username' then 'tes'
when 'email_password' then '' end
where array_key in ('email_type','email_host','email_port','email_username','email_password')
I don’t see any difference with your previous query? So my answer remains the same as well.
let’s hit him over the head with a 2-by-4…
your table contains keys ‘smtp_host’ and ‘smtp_port’
you’re trying to update the values for keys ‘email_host’ and ‘email_port’
it’s just not gonna happen
Ohhhh dear sorry guys!! I need some more coffee to keep me awake.