#1
  1. I lov C in AIX/Linux, hate C++
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2003
    Location
    Jacksonville, Florida
    Posts
    1,655
    Rep Power
    34

    Update unique row data in table using multiple rows data?


    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]
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    for starters, you need to redesign the table to have multiple address columns

    but how to extract the multiple column values from the multiple rows, that's gonna be a problem...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo