November 15, 2016 at 11:45 am
I'm fairly new to SQL and have become completely stuck in trying to do this:
I want to update a column in Table 1 where there are a number of duplicates. However, I only want to update one of the duplicate rows, and I need to update this from a value in another table.
Table 1:
name age city country
paul 30 dallas
paul 32 dallas
jim 40 chicago
pete 50 london
phil 45 munich
jim 28 chicago
Table 2:
name city country
paul dallas USA
jim chicago USA
pete london UK
phil london UK
E.g. I want to compare the fact that I have an entry for paul, dallas in both tables. I look up the country column in Table 2 and I want to apply this to the country column in Table 1. However, I want to apply it to the first occurrence only so that I end up with this:
Table 1:
name age city country
paul 30 dallas USA
paul 32 dallas
jim 40 chicago USA
pete 50 london UK
phil 45 munich
jim 28 chicago
I accept that this example probably doesn't make a lot of sense but any help on how to achieve what I'm attempting would be very much appreciated. In 'real life' my country column is a value that ends up getting double or tripled when I only want one value in there.
I think I should be using row_number() somehow but not sure how to apply the correct syntax in an UPDATE.
Thanks - and please let me know if I can clarify.
November 15, 2016 at 12:09 pm
Use a CTE or a derived table to update it using ROW_NUMBER.
WITH CTE AS(
SELECT t1.*,
t2.country AS t2_country,
ROW_NUMBER() OVER (PARTITION BY t1.name, t1.city ORDER BY t1.age) rn
FROM Table1 t1
JOIN Table2 t2 ON t1.name = t2.name AND t1.city = t2.city
)
UPDATE CTE
SET country = t2_country
WHERE rn = 1;
Read more about this option by searching for "Updateable views"
November 15, 2016 at 12:13 pm
paul.keohan (11/15/2016)
I'm fairly new to SQL and have become completely stuck in trying to do this:I want to update a column in Table 1 where there are a number of duplicates. However, I only want to update one of the duplicate rows, and I need to update this from a value in another table.
Table 1:
name age city country
paul 30 dallas
paul 32 dallas
jim 40 chicago
pete 50 london
phil 45 munich
jim 28 chicago
Table 2:
name city country
paul dallas USA
jim chicago USA
pete london UK
phil london UK
E.g. I want to compare the fact that I have an entry for paul, dallas in both tables. I look up the country column in Table 2 and I want to apply this to the country column in Table 1. However, I want to apply it to the first occurrence only so that I end up with this:
Table 1:
name age city country
paul 30 dallas USA
paul 32 dallas
jim 40 chicago USA
pete 50 london UK
phil 45 munich
jim 28 chicago
I accept that this example probably doesn't make a lot of sense but any help on how to achieve what I'm attempting would be very much appreciated. In 'real life' my country column is a value that ends up getting double or tripled when I only want one value in there.
I think I should be using row_number() somehow but not sure how to apply the correct syntax in an UPDATE.
Thanks - and please let me know if I can clarify.
First, based on sample data and expected results your data is missing any means of identifying which row is first. Remember, a table does not ensure order.
The following code uses age in ascending order to determine which row of data to update:
create table #Table1(name varchar(10),age int, city varchar(12), country varchar(3));
create table #table2(name varchar(10), city varchar(12), country varchar(3));
insert into #Table1(name,age,city)
values ('paul', 30, 'dallas')
,('paul', 32, 'dallas')
,('jim', 40, 'chicago')
,('pete', 50, 'london')
,('phil', 45, 'munich')
,('jim', 28, 'chicago');
insert into #table2
values ('paul', 'dallas', 'USA')
,('jim', 'chicago', 'USA')
,('pete', 'london', 'UK')
,('phil', 'london', 'UK');
with base as (
select
name,
age,
city,
country,
rn = ROW_NUMBER() over (partition by name order by age)
from
#Table1
)
update b set
country = t2.country
from
base b
inner join #table2 t2
on b.name = t2.name and b.city = t2.city
where
b.rn = 1;
select * from #Table1;
drop table #Table1;
drop table #table2;
November 16, 2016 at 10:14 am
Thanks for the excellent suggestions. Luis, the CTE option appears to work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply