Duplicate data in one column, change another col

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate data in one column, change another col

Author

Topic

carillonator
Starting Member

5 Posts

Posted - 2009-02-25 : 16:44:32

Hi,

I have some data that looks like this:


ID  name                  code

1   American Pie	  124
2   American Pie	  125
3   Amir's Falafel	  129
4   Amir's Falafel	  128
5   Amir's Falafel        127
6   Ammos	          5509
7   Ammos	          5510

The rows aren't totally duplicate; there is more data off to the right that is unique to each. For each set of duplicates, I would like to change the code of each to equal the lowest of the codes already there. So I would like the code for each "Amir's Falafel" to be 127, each "Ammos" to be 5509, etc. ID is the primary key.

I am using Access 2007 and am fairly new to SQL.

thanks!!

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-25 : 17:07:27

This should work, although I'm sure some other people here might have a more elegant solution:

update t
set t.code = t1.min_code
from Table1 t
join (select name, min(code) as min_code
from Table1 group by name) t1 on t.name = t1.name

Hope that helps.

Hmmm, not sure if the SQL will translate exactly into Access 2007....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 17:32:34

You even don't need this if you want all rows.

Select ID,name,code
from
(Select ID,name,ROW_NUMBER() OVER (Partition by name Order by code)as RID,
MIN(code) Over (Partition by name) as code
from TABLE)Z

carillonator
Starting Member

5 Posts

Posted - 2009-02-26 : 09:21:52

thanks for the responses.

I got only syntax errors for both of these.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-26 : 09:23:03

quote:
Originally posted by carillonator

thanks for the responses.

I got only syntax errors for both of these.

Post your query.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-26 : 23:41:52

Try this once

declare @temp table (ID int, name varchar(32),code int)
insert into @temp
select 1, 'American Pie' , 124 union all
select 2, 'American Pie', 125 union all
select 3, 'Amirs Falafel', 129 union all
select 4, 'Amirs Falafel', 128 union all
select 5, 'Amirs Falafel' , 127 union all
select 6 , 'Ammos' , 5509 union all
select 7 , 'Ammos' , 5510

update @temp
set code = t.code
from @temp t1
inner join ( select *,row_number() over( partition by name order by code) as rn from @temp ) t on t.name = t1.name
where t.rn = 1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-27 : 23:24:16

[code]Update m
set m.code = t.code
from Table m
inner join (Select ID,name,
MIN(code) Over (Partition by name) as code
from Table)t
on t.name = m.name[/code]

carillonator
Starting Member

5 Posts

Posted - 2009-03-04 : 16:23:12

Hi sodeep,

thanks so much. I've tried this identically as you included in both Access and MySQL and am getting a syntax error from both at t.code.

Just to be sure, and sorry if this is dumb: 'm' is the table I've got the data in, and 't' is a temporary table? Do I need to make this table t?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 16:34:51

Did you try SQLforGirls approach? You need to post in other forums for MySQL or in Access Topic.

carillonator
Starting Member

5 Posts

Posted - 2009-03-04 : 16:53:43

Yes, but also with syntax errors. My problem is that I don't understand exactly where to put my table name, and where t and m in your approach come from (or Table1, t and t1 in hers).

I thought that the SQL command would be the same for Access, SQL Server, and MySQL. Am I wrong?

I also am not familiar with UPDATE ... FROM.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 17:50:21

quote:
Originally posted by carillonator

Yes, but also with syntax errors. My problem is that I don't understand exactly where to put my table name, and where t and m in your approach come from (or Table1, t and t1 in hers).

I thought that the SQL command would be the same for Access, SQL Server, and MySQL. Am I wrong?

I also am not familiar with UPDATE ... FROM.

t and m are just alias for Tablename. You can use orginal table also if you need.

carillonator
Starting Member

5 Posts

Posted - 2009-03-05 : 09:44:12

I think I figured out the problem: FROM is apparently not valid syntax for UPDATE in both Access and MySQL, which is what I've been trying this in.

I'll have to try in a different forum. thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 12:39:15

quote:
Originally posted by carillonator

I think I figured out the problem: FROM is apparently not valid syntax for UPDATE in both Access and MySQL, which is what I've been trying this in.

I'll have to try in a different forum. thanks for the help.

try your luck at www.dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources