asked on

insert else update

CREATE TABLE `host_email` (
`host_email_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`event_code_id` int(11) DEFAULT NULL,
`event_code_admins_id` int(11) DEFAULT NULL,
`host_email_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`host_email_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`host_email_sent` tinyint(4) DEFAULT NULL,
`host_email_first_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`host_email_last_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`guest_user_id` int(11) DEFAULT NULL,
`host_email_type` int(11) DEFAULT NULL,
`course1` int(11) DEFAULT NULL,
`course2` int(11) DEFAULT NULL,
`course3` int(11) DEFAULT NULL,
`course4` int(11) DEFAULT NULL,
`nominate` int(11) DEFAULT NULL,
`member` int(11) DEFAULT NULL,
`host_email_member` int(11) DEFAULT NULL,
`additional_notes` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`host_email_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19682 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

insert into host_email (event_code_id,event_code_admins_id,user_id,host_email_name,host_email_first_name,host_email_last_name,host_email_email,host_email_type,host_email_member,nominate,course1,course2,course3,course4,additional_notes) select '87','72','11688','robert jones','robert','jones','email@email.com',4, '1', '', '1', '6', '3', '', 'almost vegitarian' from dual where not exists (select 1 from host_email where host_email_email = 'email@email.com' and event_code_id='87' and event_code_admins_id='72' and user_id='11688' and host_email_type=4);

but also want to update if
host_email_email,event_code_id,event_code_admins_id,user_id,host_email_type
is already there

I think it would be too difficult to have 6 keys in one table,
so I think command would be easier

Ray Paseur

You can use one key which references multiple columns. You might consider REPLACE INTO (which is a unique-to-MySQL extension of the standard). You might consider ON DUPLICATE KEY UPDATE.

rgb192

ASKER

changed to replace into
'new value'

replace into host_email (event_code_id,event_code_admins_id,user_id,host_email_name,host_email_first_name,host_email_last_name,host_email_email,host_email_type,host_email_member,nominate,course1,course2,course3,course4,additional_notes) select '87','72','11688','new value','robert','jones','email@email.com',4, '1', '', '1', '6', '3', '', 'almost vegitarian' from dual where not exists (select 1 from host_email where host_email_email = 'email@email.com' and event_code_id='87' and event_code_admins_id='72' and user_id='11688' and host_email_type=4);

Open in new window

no change in db

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

replace link provides examples,
thanks