duplicating values to another row

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

duplicating values to another row

Author

Topic

jcoleman_11
Starting Member

11 Posts

Posted - 2010-08-03 : 18:45:39

Alright, here's what i need to know, but have no idea if its possible. im hoping someone can help me out

Basically imagine my table looks something like this:

Drawer____Subdrawer____Pocket____ItemID___PktPosHi___PktPosLow
--------------------------------------------------------------
2_________1____________A_________1234
2_________1____________A__________________55555______55555
2_________1____________B_________1235
2_________1____________B__________________55556______55556

I need to find out how to duplicate the values of "PktPosHi" and "PktPosLow" the lines that have an itemID

singularity
Posting Yak Master

153 Posts

Posted - 2010-08-03 : 19:21:58

select Drawer, Subdrawer, Pocket, max(ItemID) as ItemID, max(PktPosHi) as PktPosHi, max(PktPosLow) as PktPosLow
from yourtable
group by Drawer, Subdrawer, Pocket

jcoleman_11
Starting Member

11 Posts

Posted - 2010-08-03 : 19:42:25

im looking for more of an update command though, i need it to look like this...

Drawer____Subdrawer____Pocket____ItemID___PktPosHi___PktPosLow
--------------------------------------------------------------
2_________1____________A_________1234_____55555______55555
2_________1____________A__________________55555______55555
2_________1____________B_________1235_____55556______55556
2_________1____________B__________________55556______55556

jcoleman_11
Starting Member

11 Posts

Posted - 2010-08-03 : 20:51:04

well i just ended up using Sybase Central to modify each row manually. If anyone has a script though, i could use it in the future!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-03 : 23:05:54

[code]
update t
set PktPosHi = r.PktPosHi,
PktPosLow = r.PktPosLow
from yourtable t
inner join
(
select Drawer, Subdrawer, Pocket, max(ItemID) as ItemID, max(PktPosHi) as PktPosHi, max(PktPosLow) as PktPosLow
from yourtable
group by Drawer, Subdrawer, Pocket
) r ON t.Drawer = r.Drawer and t.Subdrawer = r.Subdrawer and t.Pocket = r.Pocket
where t.PktPosHi is null
or t.PktPosLow is null
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-04 : 01:51:18

quote:

Originally posted by khtan


update t
set    PktPosHi = r.PktPosHi,
       PktPosLow = r.PktPosLow
from   yourtable t
       inner join
       (
           select Drawer, Subdrawer, Pocket, max(ItemID) as ItemID, max(PktPosHi) as PktPosHi, max(PktPosLow) as PktPosLow
           from yourtable
           group by Drawer, Subdrawer, Pocket
       ) r   ON t.Drawer = r.Drawer and t.Subdrawer = r.Subdrawer and t.Pocket = r.Pocket
where  t.PktPosHi is null
or     t.PktPosLow is null

KH
[spoiler]Time is always against us[/spoiler]

nice code easy to understand

With Regards
Kashyap M

jcoleman_11
Starting Member

11 Posts

Posted - 2010-08-04 : 13:37:23

wow, thanks! anxious to try this out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources