Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Unanswered: UPDATE using an alias to updated table

    I want to do an UPDATE using an alias to the same table.

    For example, consider if I was to have a table of registrations with an id number, year of registration and a flag to indicate whether the person had been registered previously. I wish to do something like this:
    CREATE TABLE #mytest(
    id_num CHAR(8),
    year CHAR(2),
    returner CHAR(1)
    )
    INSERT INTO #mytest (id_num,year,returner) VALUES ('0222222','03','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('0222222','04','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('0222222','05','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('0111111','03','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('0111111','04','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('0333333','04','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('9666666','03','N')
    INSERT INTO #mytest (id_num,year,returner) VALUES ('9777777','05','N')
    UPDATE #mytest
    SET returner = 'Y'
    FROM #mytest m2
    WHERE #mytest.id_num = m2.id_num
    AND #mytest.year != m2.year
    AND #mytest.year > m2.year
    SELECT * FROM #mytest
    DROP TABLE #mytest
    GO


    The error I get from running this is:
    The column prefix '#mytest' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.

    I have also tried altering the update part to:
    UPDATE m1
    SET returner = 'Y'
    FROM #mytest m1, #mytest m2
    WHERE m1.id_num = m2.id_num
    AND m1.year != m2.year
    AND m1.year > m2.year

    Which results in the error:
    m1 not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

    I realise I could use a temp table but surely there must be a way to do this sort of thing via a simple UPDATE command?

  2. #2
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43
    you should try this :

    UPDATE #mytest
    SET m1.returner = 'Y'
    FROM #mytest m1, #mytest m2
    WHERE m1.id_num = m2.id_num
    AND m1.year != m2.year
    AND m1.year > m2.year

  3. #3
    Join Date
    Jul 2004
    Posts
    12

    yippee

    Excellent stuff... works a treat.

    Thanks for the help

Posting Permissions

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