CODEWITHSUNDEEP
sqlsql-server
Oday Salim
Oday Salim

Reputation: 1147

Update Table From Another Table (Update Select)

There is an application which records transactions from projects and various other data.

There are, however, a few extra columns that are not filled it at the time of a transaction. Data for these columns are housed in a separate table in database that lists all projects.

Example below:

Transaction Table - You see in this example, the application will fill TransID, Project and Country. However, Task and Org are not filled by app.

enter image description here

Project Table - This is the main projects database.

enter image description here

Problem

I need to update the transaction table 'task' and 'org' columns with data from 'project table'. I thought it would be something as simple as:

UPDATE TABLE Transaction_Table A SET A.TASK =
(SELECT B.TASK FROM Project_Table B WHERE B.Project = A.Project AND B.Country = A.Country)
, A.Org = 
(SELECT B.Org FROM Project_Table B WHERE B.Project = A.Project)

Can someone please advise on best method to get result I want?

Thanks

Upvotes: 0

Views: 106

Answers (2)

Rajat
Rajat

Reputation: 687

you can use this query in General:

UPDATE table1
SET table1.column = table2.expression1
FROM table1
INNER JOIN table2
ON (table1.column1 = table2.column1)
[WHERE conditions];

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

just use inner join between that two tables and update as usual way 

    UPDATE A
    set A.Task=P.Task,
        A.Org=P.Org
    from
    Transaction_Table A inner join
    Project_Table P on A.Project=P.Project and A.Country=P.Country

Upvotes: 1

Related Questions