I'm trying to update the table with the script:

USE `event`; UPDATE TABLE `events_log` SET `users_id` = (SELECT `id` FROM `users_cash`.`users` WHERE `users_cash`.`users`.`login` LIKE ( SELECT `context_data` FROM `event`.`events_log`) );

But I get the error:

Error Code: 1064 You have an error in your SQL syntax; users_cash on the line, you users_cash on line 1

The events_log table is in the event database:

 CREATE TABLE `events_log` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `events_id` int(11) NOT NULL, `context_data` text, `users_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `FK_events_log` (`events_id`), KEY `fk_events_log_users_id` (`users_id`), CONSTRAINT `FK_events_log` FOREIGN KEY (`events_id`) REFERENCES `events` (`id`), CONSTRAINT `fk_events_log_users_id` FOREIGN KEY (`users_id`) REFERENCES `users_cash`.`users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=151270 DEFAULT CHARSET=cp1251

and the users table is in the users_cash database:

 CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(15) NOT NULL DEFAULT '' PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=cp1251 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

I want to make an event field. events_log . users_id equal to the users_cash field. users . id , and select this id by matching the users_cash fields. users . login and event . events_log . context_data . Why does an error occur, and how to write the correct request for this update?

Closed due to the fact that off-topic participants pavel , cheops , Denis , HamSter , aleksandr barakin Oct 27 '16 at 11:36 .

It seems that this question does not correspond to the subject of the site. Those who voted to close it indicated the following reason:

  • "The question is caused by a problem that is no longer reproduced or typed . Although similar questions may be relevant on this site, solving this question is unlikely to help future visitors. You can usually avoid similar questions by writing and researching a minimum program to reproduce the problem before publishing the question. " - pavel, cheops, Denis, HamSter, aleksandr barakin
If the question can be reformulated according to the rules set out in the certificate , edit it .

  • UPDATE without TABLE write - ilyaplot
  • @ilyaplot, yes, exactly, thanks! did not notice) issue as an answer :) - Ksenia

1 answer 1

 UPDATE`events_log` SET `users_id` = (SELECT `id` FROM `users_cash`.`users` WHERE `users_cash`.`users`.`login` LIKE ( SELECT `context_data` FROM `event`.`events_log`) );

You just need to remove TABLE from the query.