none
Update Query

    Question

  • i have two tables

    Master                                                                         Transaction

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

    AcID     FacID      PatNO                                    AcID                     FacID                    PatNo

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

    1            34           39486                                                             34                         39485

    2            45           56779                                                             34                         39486

    3            45          56777                                                              91                         12349

    4            34          39485                                                              91                         123456

    5            90           12349                                                             45                          56777

    6            91            12349                                                            45                          56779

     

    I want to update the AcID in Transaction with this condition

    Master.FacID=Transaction.FacID and

    Master.PatNO=Transaction.PatNO

     

    There are millions of records in both the tables.  I need a single update stmt.  which would do this.

    Please suggest a query for this.

    Thanks.

     

    Friday, February 29, 2008 2:52 PM

Answers

  • Here it is,

    Code Snippet

     

    Create Table #master (

                    [AcID] int ,

                    [FacID] int ,

                    [PatNO] int

    );

     

    Insert Into #master Values('1','34','39486');

    Insert Into #master Values('2','45','56779');

    Insert Into #master Values('3','45','56777');

    Insert Into #master Values('4','34','39485');

    Insert Into #master Values('5','90','12349');

    Insert Into #master Values('6','91','12349');

     

    Create Table #transaction (

                    [AcID] Varchar(100) ,

                    [FacID] Varchar(100) ,

                    [PatNo] Varchar(100)

    );

     

    Insert Into #transaction Values(NULL,'34','39485');

    Insert Into #transaction Values(NULL,'34','39486');

    Insert Into #transaction Values(NULL,'91','12349');

    Insert Into #transaction Values(NULL,'91','123456');

    Insert Into #transaction Values(NULL,'45','56777');

    Insert Into #transaction Values(NULL,'45','56779');

     

     

    update target

    Set

                    [AcID] = Source.[AcID]

    from

                    #transaction target

                                    Join #master source

                                                    on source.[FacID] = target.[FacID] and source.[PatNo] = target.[PatNo]

     

    select * from #transaction

     

     

     

     

    Friday, February 29, 2008 2:57 PM
  •  

     

     

     

    Code Snippet

    UPDATE [Transaction]

    SET [Transaction].ACID = [MASTER].ACID

    FROM [Transaction] JOIN [MASTER]

    ON [Master].FacID=[Transaction].FacID

    WHERE [Master].PatNO=[Transaction].PatNO

     

     

    Friday, February 29, 2008 2:58 PM

All replies

  • Here it is,

    Code Snippet

     

    Create Table #master (

                    [AcID] int ,

                    [FacID] int ,

                    [PatNO] int

    );

     

    Insert Into #master Values('1','34','39486');

    Insert Into #master Values('2','45','56779');

    Insert Into #master Values('3','45','56777');

    Insert Into #master Values('4','34','39485');

    Insert Into #master Values('5','90','12349');

    Insert Into #master Values('6','91','12349');

     

    Create Table #transaction (

                    [AcID] Varchar(100) ,

                    [FacID] Varchar(100) ,

                    [PatNo] Varchar(100)

    );

     

    Insert Into #transaction Values(NULL,'34','39485');

    Insert Into #transaction Values(NULL,'34','39486');

    Insert Into #transaction Values(NULL,'91','12349');

    Insert Into #transaction Values(NULL,'91','123456');

    Insert Into #transaction Values(NULL,'45','56777');

    Insert Into #transaction Values(NULL,'45','56779');

     

     

    update target

    Set

                    [AcID] = Source.[AcID]

    from

                    #transaction target

                                    Join #master source

                                                    on source.[FacID] = target.[FacID] and source.[PatNo] = target.[PatNo]

     

    select * from #transaction

     

     

     

     

    Friday, February 29, 2008 2:57 PM
  •  

     

     

     

    Code Snippet

    UPDATE [Transaction]

    SET [Transaction].ACID = [MASTER].ACID

    FROM [Transaction] JOIN [MASTER]

    ON [Master].FacID=[Transaction].FacID

    WHERE [Master].PatNO=[Transaction].PatNO

     

     

    Friday, February 29, 2008 2:58 PM
  • Thanks for the answer.

     

    Friday, February 29, 2008 3:11 PM