Couldn''t be a more Newbie question.

Question

Post reply

Couldn''t be a more Newbie question.

ryan-224331

Grasshopper

Points: 21
More actions
September 29, 2005 at 1:51 pm

#166100

I am struggling to do this simple task.

I have 2 tables:

table1 has the primary key: name
table2 has 3 fields: name, description, code

Let's say I have records like this:

name
description
code
1
bill
tan
t
2
bill
beige
b
3
bob
tan
t
4
ben
beige
b

In this example I want to combine tan and beige into one category- lightbrown. I know how to update records but not sure what to do because I do not want duplicates like I would have for ‘bill’.

Thanks for any help.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55553 More actions · Answer 1

What sort of results are you looking for ? You want to update the table to look something like this ?

name

description

code

1

bill

lightbrown

lb ?

3

bob

lightbrown

lb

4

ben

lightbrown

lb

ryan-224331 Grasshopper Points: 21 More actions · Answer 2

I am sorry, that is exactly right. The duplicate would then be totally gone. the 1, 2, 3, 4 were just numbers for the rows.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

I'd say split the action into 2 parts. First, find all names that have both tan and beige and update 'tan' to 'lightbrown' + 't' to 'lb' in them. Do not update the beige. Second step, delete all rows with beige, where exists row with the same name and 'lightbrown' in desription.

This SQL should do it, but it is untested and since I was using your column names, it contains reserved words (description, name)... I'm not sure whether you posted real names of your columns or whether it was just an example. Be careful when testing it.

UPDATE t2

SET description = 'lightbrown', code = 'lb'

FROM table2 t2

WHERE t2.description = 'tan'

AND EXISTS (select * from table2 where name = t2.name and description = 'beige')

DELETE t2

FROM table2 t2

WHERE t2.description = 'beige'

AND EXISTS (select * from table2 where name = t2.name and description = 'lightbrown')

However, this works on the assumption that there are no relations to other tables that would need to be updated... If there is another table that uses one of the columns of table2 as FK (though then I'd suppose that table2 would have some ID column), you also have to check for all rows that are connected with the row you are about to delete, and change the value in FK first to that of the 'lightbrown' row. If you need to cover these problems as well, please give us all the necessary info about relations in your DB.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Easiest is to do it in two steps

UPDATE Table2 SET Description='LightBrown', code='lb' WHERE Description IN ('tan','beige')

Then to remove duplicates

DELETE FROM Table2 WHERE ID NOT IN

(SELECT MIN(ID) FROM Table2

GROUP BY Name, Description, Code

)

HTH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Now that I see Gila's post, it seems that I have misunderstood the question. The SQL I posted only changes tan and beige to lightbrown in those cases, where both these colours are used with the same name. This is probably a nonsense... but I'm not sure

Anyway, the warning about possible problems with relations and keys still applies.

ryan-224331 Grasshopper Points: 21 More actions · Answer 6

Thanks all. Those are fake names to protect the innocent. I should have been more careful about what I chose.

It looks like what Gala posted is what I want to do. If I post again I will try to do better at my posts.

Ok, I guess I was thinking I should have done it all in one step, but that makes a lot of sense.

Thanks again.