Generally update statement will not allow updating
duplicated records; records must be unique if you want to update a table.
But sometimes your business needs duplicate records in a
table. Then your task is to provide the solution. How do you do that?
You need to make a new column that can generate sequential
numbers and you need to add that column to duplicate rows to make a unique row.
For example:
Your data look like below, say table name is Weather_DK:
Country
|
CTY
|
AVG_TEMP
|
AVG_Humidity
|
DK
|
CPH
|
12.3
|
83%
|
DK
|
CPH
|
12.3
|
83%
|
DK
|
CPH
|
12.3
|
83%
|
DK
|
Arhus
|
11.3
|
85%
|
DK
|
Aalborg
|
12.3
|
87%
|
For example, now you want to update the data in Weather_SE
table which has same column name as Weather_DK table and data like below:
Country
|
AVG_TEMP
|
AVG_Humidity
|
SE
|
19.3
|
43%
|
SE
|
23.3
|
83%
|
SE
|
12.3
|
53%
|
Fig 1.2: Table Weather_SE
Say; your business informed, Swedish average tempreture and humidity is exactly same as Denmark's capital city Copenhagen's (CPH) average temperature and humidity.
So your job is to update the Weather_SE table’s two column
AVG_TEMP and AVG_Humidity from
Weather_DK data. When you write SQL query like below:
Update Weather_SE
Set
AVG_TEMP= DK.AVG_TEMP
AVG_Humidity=DK.AVG_Humidity
from
(Select AVG_TEMP , AVG_Humidity
From Weather_DK
group by 1,2) as DK
where DK.CTY='CPH'
Set
AVG_TEMP= DK.AVG_TEMP
AVG_Humidity=DK.AVG_Humidity
from
(Select AVG_TEMP , AVG_Humidity
From Weather_DK
group by 1,2) as DK
where DK.CTY='CPH'
When you will run the above
script you will get error :”Failure 7547 Target row updated by multiple source rows”
To eliminate the error, you need
to have unique rows. So you add a new columns with your result set, you can
select like below:
select ROW_NUMBER() over
(order by
AVG_TEMP) as row_S, AVG_TEMP, AVG_Humidity
from Weather_DK as DK
Now you can easily update.
2 comments:
It should be something like:
Update Weather_SE
Set
AVG_TEMP= UPD_ABG_TEMP
AVG_Humidity= UPD_AVG_Humidity
from
(Select AVG_TEMP , AVG_Humidity
From Weather_DK
group by 1,2
)as DK (UPD_ABG_TEMP, UPD_AVG_Humidity)
where CTY='CPH';
note - this is not tested as well.
Many thanks!, Mobesar.
Post a Comment