DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: update from record in same table

Threaded View

  1. #1
    Join Date
    Feb 2005

    update from record in same table

    Hi all,

    I have this table with records on services we give to customers. If changes occur in these services, the records aren't updated, but "closed" and a new record is made which references to the old record.
    The closing of the old record is done by filling the field "Removed" with "1" and the field "DateUnlockNew" with the value from field "DateUnlock" of the new record.
    The problem is the "DateUnlockNew" isn't filled in a lot of these old records.

    When executing the following query i get 713 rows returned:

    SELECT GPOld.dateUnlockNew, GPNew.dateUnlock
    FROM dbo.T_Product GPOld INNER JOIN
    dbo.T_Product GPNew ON GPOld.ndProdID = GPNew.ndFormerProdID
    WHERE (GPOld.Removed = 1) AND (GPOld.dateUnlockNew IS NULL)

    So I tried the following update statement and get 0 rows affected:

    UPDATE dbo.T_Product
    SET dateUnlockNew = GPN.dateUnlock
    FROM dbo.T_Product GPN
    WHERE (Removed = 1) and (dateUnlockNew IS NULL) and (ndProdID = GPN.ndFormerProdID)

    Can someone show me what's wrong and how to fix it?

    Thanx in advance, Jan Peter
    Last edited by JeePee; 02-18-2005 at 06:32 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center