How to update two tables under this circumstance?

• kpao

  SSC-Addicted

  Points: 406

  More actions

  June 8, 2006 at 9:49 am

  #113319

  I've two tables called TableA and TableB.

  TablesA:                                                     TableB:

      EID       Date          Remark                             EID              Date

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

      1        03/01/2006    Null                                 1              03/01/2006   

      2        03/01/2006    Null                                 2              03/01/2006 

      3        03/02/2006    Null                                 3              03/02/2006   

      4        03/01/2006    Null

   

  Result: 

  TableA(updated):

      EID       Date          Remark        

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

      1        03/01/2006    Null            

      2        03/01/2006    Null            

      3        03/02/2006    Null            

      4        03/01/2006      N

   

  I've tried this statement:

  Update TableA Set TableA.Remark = 'N' From TableB b where TableA.EID = TableB.EID and TableA.Date != b.Date

  However, the result is not my expectation.

  How to write this sql statement with the above result?

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  June 8, 2006 at 10:21 am

  #642587

  First off, you are not showing an EID of 4 in TableB for your join. 

  Try this:

  DECLARE @TableA TABLE( EID integer, [Date] smalldatetime, Remark varchar(10) NULL)

  INSERT INTO @TableA

  SELECT 1, '03/01/2006', NULL UNION ALL

  SELECT 2, '03/01/2006', NULL UNION ALL

  SELECT 3, '03/02/2006', NULL UNION ALL

  SELECT 4, '03/01/2006', NULL

  DECLARE @TableB TABLE( EID integer, [Date] smalldatetime)

  INSERT INTO @TableB

  SELECT 1, '03/01/2006' UNION ALL

  SELECT 2, '03/01/2006' UNION ALL

  SELECT 3, '03/02/2006' UNION ALL

  SELECT 4, '01/01/1900'

  UPDATE @TableA SET

       Remark = 'N'

  FROM @TableA A

     INNER JOIN @TableB B ON( A.EID = B.EID)

  WHERE A.[Date] <> B.[Date]

  SELECT * FROM @TableA

  I wasn't born stupid - I had to study.

• stax68

  SSChampion

  Points: 11711

  More actions

  June 8, 2006 at 10:42 am

  #642593

  perhaps:

  UPDATE A SET

  Remark = 'N'

  FROM

  @TableA A

  LEFT JOIN @TableB B ON A.EID = B.EID

  AND A.[Date] = B.[Date]

  WHERE

  B.EID IS NULL

   

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  June 8, 2006 at 11:44 am

  #642627

  I think it was supposed to be where the dates where not equal.  That is why I questioned the two tables not having shared EID's. 

   

  I wasn't born stupid - I had to study.

• stax68

  SSChampion

  Points: 11711

  More actions

  June 8, 2006 at 11:49 am

  #642630

  Well I think it was where there isn't a matching key/date.

  The query (+principle of charity) backs you, the data (+ same) backs me. We may never know....

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  June 8, 2006 at 11:54 am

  #642631

  I have no idea what you are saying, but I REALLY like how you put it!!!

  I wasn't born stupid - I had to study.