Updating table A.type = B.type WHERE A.id = B.id

Question

I have two tables in different databases:

In the database named CRMALPHA:

CREATE TABLE IF NOT EXISTS `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accountId` int(11) NOT NULL,
  `Type` int(11) NOT NULL,
  `fName` varchar(255) NOT NULL,
  `lName` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `workPhone` int(11) NOT NULL,
  `workPhoneExt` int(11) NOT NULL,
  `cellPhone` int(11) NOT NULL,
  `altPhone` int(11) NOT NULL,
  `altPhoneDescription` varchar(255) NOT NULL,
  `dob` date NOT NULL,
  `createdDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdById` int(11) NOT NULL,
  `notes` varchar(255) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `accountId` (`accountId`,`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12006 ;

In the DB named scottse1_lifestyle_test

CREATE TABLE IF NOT EXISTS `tbl_customers_contact_types` (
  `ContactId` int(4) NOT NULL DEFAULT '0',
  `TypeId` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ContactId`,`TypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I need to:

UPDATE crmalpha.contacts 
SET type = scottse1_lifestyle_test.tbl_customers_contact_types.TypeID 
WHERE scottse1_lifestyle_test.tbl_customers_contact_types.ContactId = crmalpha.contacts.id

This causes the following error:

#1054 - Unknown column 'scottse1_lifestyle_test.tbl_customers_contact_types.ContactId' in 'where clause'

What am I doing wrong?

EDIT

Solution was:

UPDATE crmalpha.contacts c
JOIN scottse1_lifestyle_test.tbl_customers_contact_types t
ON t.ContactId = c.id
SET c.type = t.TypeId

Does MSQL support cross DB SQL? - http://stackoverflow.com/questions/1999235/how-do-i-construct-a-cross-database-query-in-php — Preet Sangha, May 28 '13 at 19:06

Mamuz · Answer 1 · 2013-05-28T20:19:32.043

0

You have to select the table in commaseperated way:

UPDATE CRMALPHA.contacts, scottse1_lifestyle_test.tbl_customers_contact_types
SET Type = scottse1_lifestyle_test.tbl_customers_contact_types.TypeID 
WHERE scottse1_lifestyle_test.tbl_customers_contact_types.ContactId = CRMALPHA.contacts.id

edited May 28 '13 at 20:19

answered May 28 '13 at 19:06

Mamuz

1,730
12
14

Can you do a SELECT with same result, like.. `SELECT * FROM crmalpha.contacts, scottse1_lifestyle_test.tbl_customers_contact_types WHERE scottse1_lifestyle_test.tbl_customers_contact_types.ContactId = crmalpha.contacts.id` – Mamuz May 28 '13 at 19:20
Yes that works. 222 rows returned. Seems low. I have 10k contacts 700 contact_types – Lurk21 May 28 '13 at 19:28
then should the update statement working too. You have to check the rights of the sql-user for updating in these databases. – Mamuz May 28 '13 at 19:31
what mysql-version you have? – Mamuz May 28 '13 at 19:33
Please verify your problem with this article: [MySQLDev](http://dev.mysql.com/doc/refman/5.5/en/update.html) – Mamuz May 28 '13 at 19:49
Maybe "myisam joining to innodb", is your problem. – Mamuz May 28 '13 at 19:53
I have no idea the implications of that – Lurk21 May 28 '13 at 20:12
On my local machine with mysql 5.1 and root user the sql-statement in my answer works. Verify MySQL-Rights, be sure that tables are not empty, try to use only innoDB-Engines for your Tables or manage it with PHP-Code (bad idea) – Mamuz May 28 '13 at 20:31

Updating table A.type = B.type WHERE A.id = B.id

EDIT

1 Answers1