Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update values from one table to a similar table

Posted on 2013-05-26
12
Medium Priority
?
285 Views
Last Modified: 2013-05-28
delimiter $$

CREATE TABLE `email_doc` (
  `id` int(11) NOT NULL auto_increment,
  `unix_timestamp` bigint(20) default NULL,
  `from_email` varchar(200) default NULL,
  `from_name` varchar(200) default NULL,
  `to_email` varchar(200) default NULL,
  `to_name` varchar(200) default NULL,
  `subject` varchar(400) default NULL,
  `body` varchar(4000) default NULL,
  `real_id` varchar(30) default NULL,
  `checked` int(11) default NULL,
  `me_description` varchar(9000) default NULL,
  `client_description` varchar(4000) default NULL,
  `sms_type` tinyint(4) default NULL,
  `client_start` datetime default NULL,
  `client_end` datetime default NULL,
  `client_total` int(11) default NULL,
  `me_start` datetime default NULL,
  `me_end` datetime default NULL,
  `me_total` int(11) default NULL,
  `real_timestamp` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unix_timestamp` (`unix_timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=1758 DEFAULT CHARSET=utf8$$



want to copy me_start, me_end,me_total into email_doc_similar where unix_timestamp is the same
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39198299
update email_doc_similar a set a.me_start = b.me_start
inner join email_doc b on a.id= b.id
where
a.unix_timestamp = b.unix_timestamp

------- assuming the table structure is same for both tables

repeat update statement for me_end & me_total
0
 

Author Comment

by:rgb192
ID: 39198438
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join email_doc  b on a.id= b.id where a.unix_timestamp = b.unix_times' at line 2
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39198459
update email_doc_similar a
set
a.me_start = b.me_start
a.me_end = b.me_end
a.me_total = b.me_total
from
email_doc_similar a
inner join
email_doc b on a.id= b.id
where
a.unix_timestamp = b.unix_timestamp


try this
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:rgb192
ID: 39199412
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a.me_end = b.me_end a.me_total = b.me_total from email_doc_test a inner join ema' at line 4
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39199957
update email_doc_similar a
set
a.me_start = b.me_start
from
email_doc_similar a
join
email_doc b on a.id= b.id
where
a.unix_timestamp = b.unix_timestamp

how abt this?
0
 

Author Comment

by:rgb192
ID: 39202912
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from email_doc_similar a join email_doc b on a.id= b.id where a.unix_timestam' at line 4
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39203317
update email_doc_similar a , email_doc b
set
a.me_start = b.me_start
where
a.id= b.id
and
a.unix_timestamp = b.unix_timestamp


lets keep it simple ....
0
 

Author Comment

by:rgb192
ID: 39203561
0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0
0
 
LVL 16

Accepted Solution

by:
santoshmotwani earned 2000 total points
ID: 39203568
update email_doc_similar a , email_doc b
set
a.me_start = b.me_start
where
a.unix_timestamp = b.unix_timestamp
0
 

Author Comment

by:rgb192
ID: 39203582
0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0
0
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39203609
as you said you want to copy contents from email_doc to email_doc_similar right?

if yes, please post the output for :

select * from email_doc

Thanks
0
 

Author Closing Comment

by:rgb192
ID: 39203619
this works,
comparing two tables and doing updates

thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

688 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