asked on

update values from one table to a similar table

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

santoshmotwani

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

rgb192

ASKER

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

santoshmotwani

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

rgb192

ASKER

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

santoshmotwani

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?

rgb192

ASKER

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

santoshmotwani

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

rgb192

ASKER

0 row(s) affected Rows matched: 0 Changed: 0 Warnings: 0

ASKER CERTIFIED SOLUTION

santoshmotwani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

0 row(s) affected Rows matched: 0 Changed: 0 Warnings: 0

santoshmotwani

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

rgb192

ASKER

this works,
comparing two tables and doing updates

thanks