SQL - Updating Data in a pattern of 3

1

SQL
Hi Guys-

Sorta a college alumni - SQL newbie here, Wondering a way to updating 3 items with repeating numbers (triplets), without having to type/change in the ItemIds manually (see example) like 4018, 4019, 4020...etc...etc..Then changing to 4021, 4022, 4023.

update Item
set AltName = SectionSize+'PLN'
where ItemID = 4015

update Item
set AltName = SectionSize+'SRTD'
where ItemID = 4016

update Item
set AltName = SectionSize+'SMTH'
where ItemID = 4017

In that note; Is it easy to add in doubles (ie. odd, even variables) where I can change all the odd numbers or even numbers.. for if I have doubles to change.

2

from what i can see .. its very easy to do ?

is this what you mean ?
image

3

How many rows does the update involve and is there a root number and a end number? Will there always be a unit of 3 in the ser of itens to update

4

Harishgg1 - Yes, I was wondering how easy it would be to do, without having to also change the numbers in the SQL queries.. I'm renaming the part to ' SectionSize' plus adding some verbage (PLN, SRTD, or SMTH)

Yosiasz - It will always be a unit of 3.

5

what does this mean ?
I was wondering how easy it would be to do, without having to also change the numbers in the SQL queries..

should I go ahead and implement what i showed in the Excel Picture ?

6

There are 1,220 rows (basically 4015-5235); And yes, there only be 3, in the order of (PLN, SRTD, SMTH, PLN, SRTD, SMTH, PLN, SRTD, SMTH, PLN, SRTD, SMTH...etc.)

7

Harishgg1 - yes, that would be perfect... thanks in advance!

8

There are 1,220 rows (basically 4015-5235); And yes, there only be 3, in the order of (PLN, SRTD, SMTH, PLN, SRTD, SMTH, PLN, SRTD, SMTH, PLN, SRTD, SMTH...etc.)

9

Looking at the data no need for all 3 numbers etc

Simple Case Statement .. Please see

create table WhatEver 
(
ItemID int , 
SectionSize varchar(100), 
AltName varchar(100) null
)

insert into WhatEver select 4015,'1-1 Plain',null
insert into WhatEver select 4016,'1-1 Serated',null
insert into WhatEver select 4017,'1-1 Smooth',null

insert into WhatEver select 4018,'1-2 Plain',null
insert into WhatEver select 4019,'1-2 Serated',null
insert into WhatEver select 4020,'1-2 Smooth',null

insert into WhatEver select 4021,'1-3 Plain',null
insert into WhatEver select 4022,'1-3 Serated',null
insert into WhatEver select 4023,'1-3 Smooth',null

select 'Before Update', * from whatever 

UPDATE a
SET    a.altname = SectionSize + 
                  CASE WHEN SectionSize LIKE '%Plain%' THEN 'PLN'
                             WHEN SectionSize LIKE '%Serated%' THEN 'SRTD' 
                              WHEN SectionSize LIKE '%Smooth%' THEN 'SMTH' 
                   END
FROM   whatever a 

select 'After  Update', * from whatever 

drop table whatever

image
image473×512 11.7 KB

1 Like
10

NEVERMIND - I see you were just showing me.

One more question - Do I have to create

insert into WhatEver select 4015,'1-1 Plain',null
insert into WhatEver select 4016,'1-1 Serated',null
insert into WhatEver select 4017,'1-1 Smooth',null

insert into WhatEver select 4018,'1-2 Plain',null
insert into WhatEver select 4019,'1-2 Serated',null
insert into WhatEver select 4020,'1-2 Smooth',null

insert into WhatEver select 4021,'1-3 Plain',null
insert into WhatEver select 4022,'1-3 Serated',null
insert into WhatEver select 4023,'1-3 Smooth',null

  • 1,220 times?
11

hi

this is what you have to do

UPDATE a
SET    a.altname = SectionSize + 'PLN'
FROM   whatever a 
WHERE SectionSize LIKE '%Plain%' 

UPDATE a
SET    a.altname = SectionSize + 'SRTD'
FROM   whatever a 
WHERE SectionSize LIKE '%Serated%'

UPDATE a
SET    a.altname = SectionSize + 'SMTH' 
FROM   whatever a 
WHERE SectionSize LIKE '%Smooth%'
12

no, that is just a sample of your data. in order to provide you with a solution we need to emulate locally on our sql server your data since we do not have access to your sql server. it is just sample data

13

Thanks Yosiaz - what if I did something some like (because all the items are in the sectiontypeId 1009)

select distinct SectionTypeID from Item
update Item
set AltName = SectionSize+'PLN'
where (SectionSize like '%PLAIN%'
AND SectionTypeID=1009)

Am I close?

14

yup that should be it

please take a backup before updating

if not ok can always revert back

15

Thanks Guys for all your help - I finally got what I needed to do with this -

update Item
set AltName = SectionSize+'.SRTD'
where (SectionTypeID = 1009 AND ItemPartNumber LIKE '%Serated%')