Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I just wanted to say that you guys RULE, a million thank you's to whoever created, and/or manages this site. KEEP UP THE GOOD WORK..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

Update unique row data in table using multiple rows data?Helpful Member! 

thread183-1683245
Read More Threads Like This One
fletchsod (Programmer)
15 May 12 17:42
With this script... No problem...

CODE

Update tblDealership SET tblDealership.Address1 = tblDms.Address1, tblDealership.Address2 = tblDms.Address2, tblDealership.Address3 = tblDms.Address3 FROM tblDms INNER JOIN tblDealership ON tblDms.Id = tblDealership.Id

But if I have mutliple rows in tblDms (multiple Ids) then how do I update the tblDealership?

CODE

DECLARE @TblDms(Id INT, Name VARCHAR(50), Address VARCHAR(50)) INSERT INTO @TblDms VALUES(1, 'Company1', '3351 Sara Dr') INSERT INTO @TblDms VALUES(2, 'Company2', '8710 Isle Cir') INSERT INTO @TblDms VALUES(2, 'Company2', 'PO BOX 1234') INSERT INTO @TblDms VALUES(2, 'Company2', '579 Adam Ln') INSERT INTO @TblDms VALUES(3, 'Company3', '1440 E Cast Ln')

As you see, "Company 2" have multiple addresses, so how do I use the UPDATE sql script above to make it work like this...

Address1 = '8710 Isle Cir', Address2 = 'PO BOX 1234', Address3 = '579 Adam Ln'

Thanks...
[/code]
Helpful Member!  markros (Programmer)
15 May 12 18:07
You need to transpose the data first, see

CODE

;with cte as (select * from (select ID, Address, row_number() over (partition by ID order by PK) as Rn -- some field that controls that order from @tblDms) X PIVOT (max(Address) for Rn IN ([1],[2],[3])) pvt) update T set Address1 = cte.[1], Address2 = cte.[2], Address3 = cte.[3] from tblDealership T inner join cte on T.ID = cte.ID

PluralSight Learning Library

fletchsod (Programmer)
15 May 12 18:59
I can make do w/ partition and row_number. I'm not too familar w/ PIVOT, so I'll have to study it some more.

Thanks for the replay.
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close