Solved

update values from one table to a similar table

Posted on 2013-05-26
12
283 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
Webinar: Deploying MySQL in production 6/22 11am

Join Percona’s Senior Operations Engineer, Daniel Kowalewski as he presents Deploying MySQL in production on Thursday, June 22, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 

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

Upcoming Webinar: Percona XtraDB Cluster 6/21 10am

Join Percona’s MySQL Practice Manager Kenny Gryp and QA Engineer, Ramesh Sivaraman as they present Percona XtraDB Cluster, Galera Cluster, MySQL Group Replication on Wednesday, June 21, 2017 at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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