how to update a table after fetching the data from other table

Question

I have two table as follows:

active_employee, with the columns id, email_id, job, location and project_id

project_relation, with the columns id, email_id and project_id.

My problem is that I want to update the project_id column of project_relation table with the condition that if the value for project_id in both the tables are not same, then it has to fetch the value from "active_employee" table and update the project_relation table for corresponding email_id.

Help me how to do this!

Possible duplicate of [MySQL UPDATE syntax with multiple tables using WHERE clause](http://stackoverflow.com/questions/15037883/mysql-update-syntax-with-multiple-tables-using-where-clause) — Shadow, Feb 19 '16 at 11:39
i tried using join but it was giving error like i cannot use target table in from clause — cr.7, Feb 19 '16 at 11:52
you should add clause with column_id and project_id and use join on project_id — Rahul, Feb 19 '16 at 12:03

Rahul · Answer 1 · 2016-02-19T12:14:31.487

I believe an UPDATE FROM with a JOIN will help:

UPDATE
    project_relation
SET
    project_relation.project_id = active_employee.project_id
FROM
    project_relation PR
INNER JOIN
    active_employee AE
ON 
    PR.columns_id = AE.columns_id

Just check underscore.. it may create problem.. you can remove it and make changes in db columns names also.

or Simply try this-

UPDATE project_relation SET project_relation.project_id = active_employee.project_id where project_relation.columns_id = active_employee.columns_id;

score 0 · Answer 2 · answered Feb 19 '16 at 12:22

I think I get what you need. I created some temp tables to replace your actual tables for testing.

I used a subquery to rename some of the column names to remove ambiguous names because of the update table:

DECLARE @ACTIVE_EMPLOYEE TABLE (columnid UNIQUEIDENTIFIER, emailid UNIQUEIDENTIFIER, job VARCHAR(200), location VARCHAR(200), projectid UNIQUEIDENTIFIER)
DECLARE @PROJECT_RELATION TABLE (columnid UNIQUEIDENTIFIER, emailid UNIQUEIDENTIFIER, projectid UNIQUEIDENTIFIER)

UPDATE @PROJECT_RELATION
SET projectid = active.proid
FROM (SELECT columnid,
             emailid [emid],
             job,
             location,
             projectid [proid]
      FROM @ACTIVE_EMPLOYEE) active
WHERE active.emid = emailid
AND active.proid <> projectid

how to update a table after fetching the data from other table

2 Answers2