update from record in same table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: update from record in same table

  1. #1
    Join Date
    Feb 2005
    Location
    Holland
    Posts
    4

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    If I understand what you want to do correctly, it should be like this:

    UPDATE dbo.T_Product
    SET 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)

    Rune
    If you hit a brick wall, you didn't jump high enough!

  3. #3
    Join Date
    Feb 2005
    Location
    Holland
    Posts
    4

    Ambiguous...

    Quote Originally Posted by Rune Bivrin
    If I understand what you want to do correctly, it should be like this:

    UPDATE dbo.T_Product
    SET 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)

    Rune
    Hi Rune,

    Thanks for the response. But now i get this message in query analyzer:

    Server: Msg 8154, Level 16, State 1, Line 1
    The table 'dbo.T_GeindProdukt' is ambiguous.


    Any thoughts?

    Jan Peter

  4. #4
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Unless you post thye actual SQL, no. I see no trace of 'dbo.T_GeindProdukt' in your query...

    Rune
    If you hit a brick wall, you didn't jump high enough!

  5. #5
    Join Date
    Feb 2005
    Location
    Holland
    Posts
    4

    retry...

    Right... Our column-names being in dutch, i thought to translate them... and then forgetting to translate again in my reply...

    So... here's the code with names as they are in our dbase:

    UPDATE dbo.T_GeindProdukt
    SET ddLosgekoppeldHerInd = GPNew.ddLosgekoppeld
    FROM dbo.T_GeindProdukt INNER JOIN dbo.T_GeindProdukt GPNew ON dbo.T_GeindProdukt.ndGeindProdID = GPNew.ndVorigGeindProdID
    WHERE (dbo.T_GeindProdukt.bdUitgepland = 1) AND (dbo.T_GeindProdukt.ddLosgekoppeldHerInd IS NULL) AND (GPNew.bdLosgekoppeld=1)

    I made two changes:
    1. Removed the "GPOld" alias from the from-clause. Seeing as i read in the T-SQL reference that, if the same table is referenced twice, one must be without an alias. Is my thinking correct?
    At least I have no more error msg's

    2. added (GPNew.bdLosgekoppeld=1) to limit the number of rows returned from GPNew... Should speed things up a bit, I think?

    Well, thanks for getting me on the right track, Rune! Much appreciated!

  6. #6
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Good to hear you got it working!

    Dutch isn't much of a problem. If you know Swedish, English and German, unserstanding written Dutch is quite easy. Spoken is another issue altogether.

    Not sure about the alias thing. Looking at UPDATE in Books Online, it's clearly permissible. I álmost always use aliases, sometimes even in single table selects, but that's me...

    Rune
    If you hit a brick wall, you didn't jump high enough!

  7. #7
    Join Date
    Feb 2005
    Location
    Holland
    Posts
    4

    Lightbulb

    from SQL Server Books Online, transact sql reference, update statement:
    "FROM < table_source >

    Specifies that a table is used to provide the criteria for the update operation. For more information, see FROM.

    table_name [ [ AS ] table_alias ]
    Is the name of a table to provide criteria for the update operation.
    If the table being updated is the same as the table in the FROM clause, and there is only one reference to the table in the FROM clause, table_alias may or may not be specified. If the table being updated appears more than one time in the FROM clause, one (and only one) reference to the table must not specify a table alias. All other references to the table in the FROM clause must include a table alias. "

    Thanks for the help and keep up the good work

    Jan Peter

  8. #8
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Doh! Didn't remember that, and it's one of those things I *know* I've butted my head against more times than I care to remember. It's what I'd like to call an arbitrary restriction.

    Rune
    If you hit a brick wall, you didn't jump high enough!

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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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