SQLTeam.com | Weblogs | Forums

Update set question


#1

In my BOM table, I have "PARENT" numbers that have multiple lines with different "PARTNUMBER". The PARTNUMBER are units needed to repair the PARENT. Example, a PARENT will take 10 unique PARTNUMBER to repair so the one PARENT will have 10 lines in the table. The 10 lines will all have the same PARENT but each line will have a unique PARTNUMBER. Ok, here is my question

Sometimes I need to replace a PARTNUMBER with a different PARTNUMBER. Here is what I normally run:

update BOM
set PARTNUMBER = '182649'
where PARTNUMBER = '2-009V747-75'

I have no problem with this and it works perfectly.

Here is my problem. I now need to modify all the PARENT's that have these three PARTNUMBER's in them ('801087008','801087009','801087010') and update to one PARTNUMBER = 'KGK002'.

If effect, I will have the query search for all PARENT's that have all three of these PARTNUMBER's('801087008','801087009','801087010') , erase the three lines, and replace with one line....PARTNUMBER ='KGK002'. How should I write the update statement please?


#2

Without your table definition, I'm kind of guessing but...[code]declare @AllThreeParts table ( -- This could be a temp table if desired
PARENT varchar(20) -- I don't know your column definition
)

-- Find all Parents that have all three parts

insert into @AllThreeParts
select PARENT
from BOM
where PARTNUMBER in ('801087008','801087009','801087010')
group by PARENT
having count(*) = 3

--- Update one of them...

update b
set PARTNUMBER = 'KGK002'
from BOM b
inner join
@AllThreeParts a
on a.PARENT = b.PARENT
where b.PARTNUMBER = '801087008' -- Randomly picked one of the three for update

-- ... and delete the other two!

delete b
from BOM b
inner join
@AllThreeParts a
on a.PARENT = b.PARENT
where b.PARTNUMBER in ('801087009','801087010')[/code]This should be wrapped in a transaction to prevent unhappy eventualities.


#3

Thank you for your response. I'm sort of new at this so I don't want to mess anything up. Our IT guy is out on extended leave so I'm it.

The current table is 'BOM'
The 'Parent' is a column in the table and it consists of number/letter combinations. '10473K' or '1024K-N-J' are examples that are in that column. The 'Partnumber' column has the same style number/letter combo's in it.

Knowing this, could you rewrite your script for me please? Thank you for your help on this!


#4

Temp tables and "don't know table definitions" concerns me. I should know what you are talking about but I don't.

I believe I have around 50 Parent's that fit this criteria so if I mess up it's not the end of the world for me. Just trying to do the script shortcut. Thank you.


#5

Does anyone know if I just enter this code below and hit F5, will it do exactly what I need from the formation I listed above? I just don't want to mess anything up. Thank you.

declare @AllThreeParts table (PARENT varchar(30))
select PARENT
from BOM
where PARTNUMBER in ('801087008','801087009','801087010')
group by PARENT
having count(*) = 3

update b
set PARTNUMBER = 'KGK002'
from BOM b
inner join
@AllThreeParts a
on a.PARENT = b.PARENT
where b.PARTNUMBER = '801087008'
delete b
from BOM b
inner join
@AllThreeParts a
on a.PARENT = b.PARENT
where b.PARTNUMBER in ('801087009','801087010')


#6

I'll elaborate on some of the terms but you can find them all in Books Online (the Help facility that comes with SQL Server).
"Temp Tables" - These are tables that can be created on the fly and automatically are dropped when the connection completes. Their names begin with "#" or "##".
"don't know table definitions" - I don't know the exact details of your table; things such as column names, data types, etc. You mention a few columns by name. It's easier to provide a suggested solution if you provide a simplified CREATE TABLE statement in your question.
"hit F5, will it do exactly what I need?" - No one can tell you that and you certainly should not just run it in your production environment. Try to run it in a test environment to see if it satisfies your needs. You could run parts of it to see if it is right. For instance, the first select is trying to find all of the PARENTs that use all three of the subparts. Run that part select PARENT from BOM where PARTNUMBER in ('801087008','801087009','801087010') group by PARENT having count(*) = 3Are those the PARENTs that you want modified?
To see what the UPDATE or DELETE statements are going to do, turn them into SELECT statements. Something along the lines ofselect * from BOM b inner join @AllThreeParts a on a.PARENT = b.PARENT where b.PARTNUMBER = '801087008' -- Randomly picked one of the three for updateAre these the ones you want to update?select * from BOM b inner join @AllThreeParts a on a.PARENT = b.PARENT where b.PARTNUMBER in ('801087009','801087010')Are these the ones you want to delete?