locked
Update data in one table from another table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have following tables

    Table A has following data with columns ID, VIN, License,State

    10 ABC123 XYZ WA
    2 ABC123 XYZ WA
    3 ABC123 XYZ WA
    4 ABC123 XYZ WA
    5 DEF456 IJK CA

    Table B has following data with columns ID, VIN, License,State,Flag columns

    10 ABC123 XYZ WA P
    2 ABC123 XYZ WA D
    3 ABC123 XYZ WA D
    4 ABC123 XYZ WA D

    I need to update Table A ID field which has have equivalent VIN,License,State and Flag D from Table B with ID which has Flag P based on VIN, License and State. So my output for table A should be 

    10 ABC123 XYZ WA
    10 ABC123 XYZ WA
    10 ABC123 XYZ WA
    10 ABC123 XYZ WA
    5 DEF456 IJK CA

    Any suggestions on how to accomplish this,

    Monday, September 24, 2012 12:44 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    update A set ID = P.ID

    from TableA A inner join TableB B on A.Id = B.Id and B.Flag = D

    INNER JOIN TableB P on B.VIN = P.VIN and B.License = P.License and B.State = P.State and P.Flag = 'P'

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by SQL Insane Monday, September 24, 2012 3:20 AM
    Monday, September 24, 2012 1:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think it looks OK except I would use

    UPDATE a

    instead of UPDATE dbo.ActualTable

    -------------

    Before running this update, put

    BEGIN TRANSACTION

    UPDATE...

    SELECT * from dbo.ActualTable --- verify

    ROLLBACK TRANSACTION

    If the result looks OK, change ROLLBACK to COMMIT.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by S_Surve Monday, September 24, 2012 3:18 AM
    • Marked as answer by SQL Insane Monday, September 24, 2012 4:26 AM
    Monday, September 24, 2012 1:23 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    update A set ID = P.ID

    from TableA A inner join TableB B on A.Id = B.Id and B.Flag = D

    INNER JOIN TableB P on B.VIN = P.VIN and B.License = P.License and B.State = P.State and P.Flag = 'P'

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by SQL Insane Monday, September 24, 2012 3:20 AM
    Monday, September 24, 2012 1:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I tried this code. Hoep this works. let me know if there is any issue with code.

    update dbo.ActualTable
    set ID = b.ID
    from dbo.ActualTable a inner join dbo.DuplicateTable b
    on a.VIN = b.VIN and a.License = b.License and a.[State] = b.[State]
    inner join dbo.DuplicateTable c
    on a.VIN = c.VIN and a.License = c.License and a.[State] = c.[State]
    where b.flag = 'P' and c.Flag = 'D'

    Monday, September 24, 2012 1:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think it looks OK except I would use

    UPDATE a

    instead of UPDATE dbo.ActualTable

    -------------

    Before running this update, put

    BEGIN TRANSACTION

    UPDATE...

    SELECT * from dbo.ActualTable --- verify

    ROLLBACK TRANSACTION

    If the result looks OK, change ROLLBACK to COMMIT.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by S_Surve Monday, September 24, 2012 3:18 AM
    • Marked as answer by SQL Insane Monday, September 24, 2012 4:26 AM
    Monday, September 24, 2012 1:23 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks you so so much.

    http://dailybanvemaybay.com.vn

    Monday, September 24, 2012 4:58 AM