Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    8

    Unanswered: Compare TWO Tables

    I want to Write a Stored Procedure ....I have 2 tables table A and Table B.. Table A has 6 columns and Table B has 7 Columns.. Data is the Same except few Rows.

    IF all the column matches then i should update as "all match" in the table B on a particular Column. if only 3 column match then "m3".... 5 column match then "m5"
    Last edited by kumarkr; 03-02-12 at 05:44.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    WITH CTE AS
    (SELECT B.Id,
    	CASE WHEN B.Col1 = A.col1 THEN 1 ELSE 0 END +
    	CASE WHEN B.Col2 = A.col2 THEN 1 ELSE 0 END +
    	...
    	CASE WHEN B.Col6 = A.col6 THEN 1 ELSE 0 END
    	AS NrEqual
    FROM B
    	LEFT OUTER JOIN A ON
    		B.Id = A.Id
    )
    UPDATE U
    SET U.col7 = CASE WHEN CTE.NrEqual = 6 
    			THEN 'all match'
    			ELSE 'm' + CAST(NrEqual as VARCHAR(1))
    		END
    FROM B as U
    	INNER JOIN CTE ON
    		U.Id = CTE.Id
    Last edited by Wim; 03-02-12 at 05:52.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2012
    Posts
    8

    stored procedure update

    H1
    select client_input.CLIENTINPUTID from Client_input inner join Client_Temp on Client_input.SOURCE=Client_Temp.SOURCE
    where Client_input.ITEM=Client_Temp.ITEM and
    Client_input.[DESC]=Client_Temp.[desc] and
    Client_input.[mfg]=Client_Temp.[mfg] and
    Client_input.[mfgid]=Client_Temp.[mfgid] and
    Client_input.[pack]=Client_Temp.[pack] and
    Client_input.[uom]=Client_Temp.[uom] and
    Client_input.[cost]=Client_Temp.[cost]

    h2
    select client_input.CLIENTINPUTID from Client_input inner join Client_Temp on Client_input.SOURCE=Client_Temp.SOURCE
    where Client_input.ITEM<>Client_Temp.ITEM and
    Client_input.[DESC]<>Client_Temp.[desc] and
    Client_input.[mfg]=Client_Temp.[mfg] and
    Client_input.[mfgid]=Client_Temp.[mfgid] and
    Client_input.[pack]=Client_Temp.[pack] and
    Client_input.[uom]=Client_Temp.[uom] and
    Client_input.[cost]=Client_Temp.[cost]



    H3
    select client_input.CLIENTINPUTID from Client_input inner join Client_Temp on Client_input.SOURCE=Client_Temp.SOURCE
    where Client_input.ITEM=Client_Temp.ITEM and
    Client_input.[DESC]=Client_Temp.[desc] and
    Client_input.[mfg]<>Client_Temp.[mfg] and
    Client_input.[mfgid]<>Client_Temp.[mfgid] and
    Client_input.[pack]<>Client_Temp.[pack] and
    Client_input.[uom]<>Client_Temp.[uom] and
    Client_input.[cost]=Client_Temp.[cost]



    H4
    select client_input.CLIENTINPUTID from Client_input inner join Client_Temp on Client_input.SOURCE=Client_Temp.SOURCE
    where Client_input.ITEM=Client_Temp.ITEM and
    Client_input.[DESC]=Client_Temp.[desc] and
    Client_input.[mfg]<>Client_Temp.[mfg] and
    Client_input.[mfgid]<>Client_Temp.[mfgid] and
    Client_input.[pack]<>Client_Temp.[pack] and
    Client_input.[uom]<>Client_Temp.[uom] and
    Client_input.[cost]<>Client_Temp.[cost]


    i want to update H1 if the first query is true H2 if second query is true correspondingly the H3 and H4

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •