Solved

update values from one table to a similar table

Posted on 2013-05-26
12
284 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

631 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