Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Unanswered: UPDATE into JOINed table - Access vs. SQL Server syntax

    I am trying to get a SQL statement to work with both Access 2000 and SQL Server 2000.

    The statement that works in SQL Server is:
    Code:
    ---
    UPDATE [myTable2] 
    
    SET 
    [myTable2].[FieldA] = 'Hello', 
    [myTable2].[FieldB] = 2, 
    [myTable2].[FieldC] = 'xxx', 
    [myTable2].[FieldD] = 0
    
    FROM  [myTable1] INNER JOIN 
                    (myTable2 INNER JOIN [myTable3] 
                                   ON [myTable2].[FieldX]=[myTable2].[FieldY]) 
                              ON [myTable1].[FieldZ]=[myTable2].[FieldY] 
    
    WHERE ([myTable2].[FieldY]=1) 
    And ([myTable3].[FieldZ]='xxx');
    ---
    (names have been changed to protect the innocent)


    The statement that works in Access is:
    Code:
    ---
    UPDATE [myTable1] INNER JOIN 
                        (myTable2 INNER JOIN [myTable3] 
                                        ON [myTable2].[FieldX]=[myTable2].[FieldY]) 
                               ON [myTable1].[FieldZ]=[myTable2].[FieldY] 
    
    SET 
    [myTable2].[FieldA] = 'Hello', 
    [myTable2].[FieldB] = 2, 
    [myTable2].[FieldC] = 'xxx', 
    [myTable2].[FieldD] = 0
    
    WHERE ([myTable2].[FieldY]=1) 
    And ([myTable3].[FieldZ]='xxx');
    ---
    It seems that neither will accept the other format. Can anyone suggest how I can rearrange the statement so that it works in both?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think you can

    the syntax is just different, and you can ask bill gates to look into it, but meanwhile you have to handle joined updates differently in different databases

    wait'll you also have to support mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Wait until you have to convert to PL/SQL no joins allowed in Inserts, updates or deletes.

Posting Permissions

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