How to update one table from another table in SQL Server?

Question

I want to update table with data in another table

Table 1 OverseasOperation and table 2 is ForecastTotal

I used this command but it copy only the first value

Update ForecastTotal 
Set OVERSEASVol = Sales 
From OverseasOperation

Result is first value in Sales = OVERSEASVol

The result is

but I want to get the table like this

What's wrong ?

Note: I want to update not insert, and don't want to make condition because I want to copy all data

Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Palpatim, Apr 16 '17 at 18:35

score 0 · Answer 1 · answered Apr 16 '17 at 18:33

0

The most generic method is to use a correlated subquery:

Update ForecastTotal
    Set OVERSEASVol = (select Sales
                       from OverseasOperation oo
                       where oo.?? = ForecastTotal.??
                      );

The ?? is for the join conditions. It is unclear what column(s) are used to connect the two tables.

Note: Many databases support some method of joining tables in the update statement. Those methods tend to depend on the database.

answered Apr 16 '17 at 18:33

Gordon Linoff

1,242,037
58
646
786

i don't want to put condition so the result is .. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. – Amr Kamal Apr 16 '17 at 18:36
@AmrKamal . . . Edit your question and provide sample data and desired results. – Gordon Linoff Apr 16 '17 at 18:43
I Edited the question with screenshot from database and what i want – Amr Kamal Apr 16 '17 at 18:49

Vecchiasignora · Answer 2 · 2017-04-16T19:37:30.270

0

try it

;with ForecastTotal_Temp
as
(
select ForecastTotal.OVERSEASVol as OVERSEASVol, row_number() over (order by OVERSEASVol) as rownum
from ForecastTotal
)

update ForecastTotal_Temp
set OVERSEASVol = (
                    select sub.Sales 
                    from (select OverseasOperation.Sales AS Sales, row_number() over(order by Sales ) as rnum from OverseasOperation) sub 
                    where sub.rnum=ForecastTotal_Temp.rownum
                  )

edited Apr 16 '17 at 19:37

answered Apr 16 '17 at 18:34

Vecchiasignora

1,275
7
6

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. – Amr Kamal Apr 16 '17 at 18:34
don't use where cause i want to copy all the column – Amr Kamal Apr 16 '17 at 18:35
but you should have connect between your tables – Vecchiasignora Apr 16 '17 at 18:36
so why its updated with the first values .. so the error isn't in conncetion – Amr Kamal Apr 16 '17 at 18:38
Kamal when you set where it is where condition on yous subquery, not on your main table, so your table all data will be updated when you will understand how can you want to get data from subquery – Vecchiasignora Apr 16 '17 at 18:43
so can u edit the answer with the sql query .. sorry for bothering u – Amr Kamal Apr 16 '17 at 18:45
i have change my answer, and described everething – Vecchiasignora Apr 16 '17 at 18:49
it is miss understanding i want to get all data not only top 1 and i haven't where condition i want to copy all data in the second column – Amr Kamal Apr 16 '17 at 18:51
please show me your tables definition and tables some data – Vecchiasignora Apr 16 '17 at 18:55
i added sql tables design anyway – Amr Kamal Apr 16 '17 at 18:59
try, i have changed – Vecchiasignora Apr 16 '17 at 19:23
try again, i have changed something – Vecchiasignora Apr 16 '17 at 19:37
-20 -10 -4 -3 -1 -1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 got numbers like this and this is wrong :/ – Amr Kamal Apr 16 '17 at 19:42
that Means you have other data in OverseasOperation table – Vecchiasignora Apr 16 '17 at 19:46

score 0 · Answer 3 · answered Apr 17 '17 at 04:23

Assuming the tables share a unique common key (call it ukey) then

update ForecastTotal
   set sales = OverseasOperation.Sales
from ForecastTotal
join OverseasOperation on ForecastTotal.ukey = OverseasOperation.ukey

If the OverSeasOperation has multiple occurences of the primary key from ForecastTotal, then the sales must be summed,grouped on the primary key and wiill serve as the second table on the join, shown here

update ForecastTotal
   set sales = oo.Sales
from ForecastTotal
join 
( 
    select ukey, sum(Sales) sales
    from OverseasOperation
    group by ukey
) oo
on ForecastTotal.ukey = oo.ukey

Also, if you wish to update the ForecastTotal sales by adding the sales from the OverseasOperation, change the set from

set sales = OverseasOperation.Sales

to

set sales = sales + OverseasOperation.Sales

How to update one table from another table in SQL Server?

3 Answers3