Results 1 to 2 of 2

Thread: Update with Inner join on same table

  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Update with Inner join on same table

    Hi!

    I try to solve following situation:

    The table looks like this:
    Code:
    pattern_id (int), sub_id (int), orderId (int), fieldname (varchar), value (varchar)
    Now I wrote a query to get a list of all fieldnames, that come (by means of order) directly before the fieldname "Year" - for this i use an inner join on the same table like in this statement (info - in the table are multplie fields with name="Year", but with different sub_id):

    Code:
    SELECT	fieldname, value
    FROM TPattern AS a
        INNER JOIN TPattern AS b ON (a.Pattern_Id=b.Pattern_Id AND a.sub_id=b.sub_id)
    WHERE (a.Fieldname='Year')
        AND (b.OrderID = (a.OrderID-1))
    Now I need to get an Update that changes the field "value" of the result of above query. I tried to do so with the follwing query, but it did not work.

    Code:
    UPDATE TPattern
        SET value="valid"
    FROM TPattern AS a
        INNER JOIN TPattern AS b ON (a.Pattern_Id = b.Pattern_Id)
    WHERE (a.Fieldname='Year')
        AND (b.OrderID = (a.OrderID-1))
    Thanks for suggestions on how to solve this.
    Philipp

  2. #2
    Join Date
    Dec 2009
    Posts
    2

    Re: Update with Inner join on same table

    I could solve this issue by using a view of the pattern table like in following code:

    Code:
    /* View as help for intra-table comparison */
    CREATE VIEW VPattern
    AS
    SELECT * FROM TPattern
    GO
    
    /* Make changes */
    UPDATE TPattern
        SET value="valid"
    FROM TPattern
        INNER JOIN TPattern AS b
        ON (TPattern.Pattern_Id=b.Pattern_Id AND TPattern.sub_id=b.sub_id)
    WHERE (TPattern.Fieldname = 'Year')
        AND (b.OrderID = (TPattern.OrderID-1))
    
    /* Delete view again since it was only needed for the update */
    DROP VIEW VPattern

    So that's it... :-)

Similar Threads

  1. Replies: 4
    Last Post: 01-06-2011, 07:21 AM
  2. Link a Table to another Table to Drop Down In Main Table
    By himeshRES in forum Windows Software
    Replies: 6
    Last Post: 11-12-2010, 02:01 PM
  3. Getting error while using UPDATE with join
    By Bottlenecked in forum Software Development
    Replies: 5
    Last Post: 18-01-2010, 01:53 PM
  4. How to Create view based on table-join query
    By Preetish in forum Windows Software
    Replies: 3
    Last Post: 12-08-2009, 01:44 PM
  5. Update with Inner Join SQL Server
    By Coldman in forum Software Development
    Replies: 5
    Last Post: 25-02-2009, 10:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,645,970.50349 seconds with 17 queries