Update rows from one table to another table based on Id

Question

Both tables have a client_id column. Need to insert data from #LocalDashboardtable into T004_Dashboard when the client_id column are equal. i have tried this but it does't help shows an error "Incorrect syntax near ','. "

    update T004_Dashboard  set T004_Dashboard.[GrossCharge],T004_Dashboard.[NetCharge] 
= (select #LocalDashboardtable.[GrossCharge] , #LocalDashboardtable.[NetCharge] 
from #LocalDashboardtable   where 
#LocalDashboardtable.client_id =T004_Dashboard.client_id and 
#LocalDashboardtable.[month] =T004_Dashboard.[month] 
and #LocalDashboardtable.[year] =T004_Dashboard.[year]  )

pls help me

You are using SQL Server syntax but the question is tagged MySQL. Which database are you really using? — Gordon Linoff, Dec 04 '14 at 03:44

score 1 · Answer 1 · answered Dec 04 '14 at 03:49

This is your query (which looks a lot like SQL Server):

update T004_Dashboard
    set T004_Dashboard.[GrossCharge],
        T004_Dashboard.[NetCharge] = (select #LocalDashboardtable.[GrossCharge], #LocalDashboardtable.[NetCharge] 
                                      from #LocalDashboardtable
                                      where #LocalDashboardtable.client_id = T004_Dashboard.client_id and 
                                            #LocalDashboardtable.[month] = T004_Dashboard.[month] and
                                            #LocalDashboardtable.[year] = T004_Dashboard.[year]
                                     );

You cannot set a pair of columns to a pair of columns in a subquery. Instead, use a join:

update T004_Dashboard
    set GrossCharge = ld.GrossCharge,
        NetCharge = ld.NetCharge
    from T004_Dashboard d join
         #LocalDashboardtable ld
         on ld.[month] = d.[month] and ld.[year] = d.[year] and
            ld.client_id = d.client_id;

Also, SQL Server does not allow qualified column names in update/set statements

Update rows from one table to another table based on Id

1 Answers1