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
LVL 1
rgb192Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ray PaseurConnect With a Mentor Commented:
I think we would need the SSCCE, including some data to test with.
http://dev.mysql.com/doc/refman/5.0/en/replace.html
0
 
Ray PaseurCommented:
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.
0
 
rgb192Author Commented:
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
0
 
rgb192Author Commented:
replace link provides examples,
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.