MySQL Table update returning error

Hi,

I am using following SQL

update email a, contact b
set a.email =  CONCAT('abcd+' ,b.first_name,', ',b.last_name, '@gmail.com')  
where a.contact_id=b.id

I have an error
 #1442 - Can't update table 'contact' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

There are no triggers on the table.

Thanks
crazywolf2010Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sriramiyerConnect With a Mentor Commented:
you can try this,

update email a, contact b
set a.email =  CONCAT('abcd' ,b.first_name,b.last_name, '@gmail.com')  
where a.contact_id=b.id
0
 
apreedCommented:
Is this a stored function? If so, check the SQL code that calls this function and make sure "contact" is not already used as an alias in there within the same session.
0
 
crazywolf2010Author Commented:
Hi,
This is not stored function at all. I am running it under phpmyadmin.

Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
nemws1Connect With a Mentor Database AdministratorCommented:
Try using the newer style syntax for such things (instead of doing a cross join)

UPDATE email AS a
  JOIN contact AS b
    ON a.contact_id = b.id
SET a.email = CONCAT('abcd', b.first_name, b.last_name, '@gmail.com')
;

Open in new window

0
 
crazywolf2010Author Commented:
Exact same error
0
 
Jagadishwor DulalBraces MediaCommented:
What is the scenario I don't understand your question are you updating using two tables ?? can you post image for your table??
0
 
nemws1Connect With a Mentor Database AdministratorCommented:
Is there a trigger on the 'contact' table?  I'm assuming when you originally stated "There are no triggers on the table." you meant the 'email' table.

Can you run this SQL command just to double-check? (replace 'your_database_name' with the name of your database)

SELECT TRIGGER_NAME
    , EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name'
;

Open in new window

0
All Courses

From novice to tech pro — start learning today.