Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

insert else update

Posted on 2013-05-20
4
Medium Priority
?
356 Views
Last Modified: 2013-05-22
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
0
Comment
Question by:rgb192
  • 2
  • 2
4 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39182754
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
 

Author Comment

by:rgb192
ID: 39182980
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39184774
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
 

Author Closing Comment

by:rgb192
ID: 39188927
replace link provides examples,
thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month5 days, 22 hours left to enroll

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question