Solved

insert else update

Posted on 2013-05-20
4
306 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 108

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 108

Accepted Solution

by:
Ray Paseur earned 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now