Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Unhappy Unanswered: Problem moving from Access to Sybase

    My sql is average. I am moving from Access to Sybase and some of the queries no longer work.
    In particular,


    UPDATE LDAP_NAMES_FILTERED, authors INNER JOIN t_authors_products ON authors.id = t_authors_products.author_id
    SET t_authors_products.NID = LDAP_NAMES_FILTERED.NID, t_authors_products.LINK_STATUS_ID = 2
    WHERE LDAP_NAMES_FILTERED.NOB_NAME = authors.author_name_caps
    AND LDAP_NAMES_FILTERED.SKIP_FLAG_ID Is Null AND t_authors_products.state = 1



    the error message is:
    Server Message: Number 102, Severity 15
    Server 'development', Line 1:
    Incorrect syntax near ','.
    Server Message: Number 102, Severity 15
    Server 'development', Line 2:
    Incorrect syntax near '='.



    This looks like a normal query. I can figure out whats wrong with it.

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    well its not like any Sybase syntax I know....

    this would be my take...

    UPDATE TAP
    SET TAP.NID = NF.NID,
    TAP.LINK_STATUS_ID = 2
    FROM authors A,
    LDAP_NAMES_FILTERED NF ,
    t_authors_products TAP
    WHERE A.id = TAP.author_id
    AND NF.NOB_NAME = A.author_name_caps
    AND NF.SKIP_FLAG_ID Is Null
    AND TAP.state = 1

    HTH

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    inner join as part of an update

    The two methods of writing joins, ANSI or Transact-SQL, are equivalent. For example, there is no difference between the result sets produced by the following queries:

    select title_id, pub_name
    from titles, publishers
    where titles.pub_id = publishers.pub_id

    and

    select title_id, pub_name
    from titles inner join publishers on titles.pub_id = publishers.pub_id


    You can specify only one table name on the update statement, hence the Incorrect syntax near ','.
    An inner join can be part of an update or delete statement by using the "from" clause


    The full syntax for update is:
    Code:
    update [[database.]owner.]{table_name | view_name}
         set [[[database.]owner.]{table_name. | view_name.}] 
              column_name1 = 
              {expression1 | null | (select_statement)} |
              variable_name1 = 
              {expression1 | null | (select_statement)} 
              [, column_name2 = {expression2 | null |
              (select_statement)}]... | 
                   variable_name2 = {expression1 | null |
              (select_statement)} 
         [from [[database.]owner.] {table_name | view_name} 
              [, [[database.]owner.] {table_name | 
              view_name}]]... 
         [where search_conditions]
    Change your update to e.g.

    Code:
    UPDATE t_authors_products 
    SET t_authors_products.NID = LDAP_NAMES_FILTERED.NID
      , t_authors_products.LINK_STATUS_ID = 2
    from LDAP_NAMES_FILTERED
       , authors INNER JOIN t_authors_products 
         ON authors.id = t_authors_products.author_id 
    WHERE LDAP_NAMES_FILTERED.NOB_NAME = authors.author_name_caps 
      AND LDAP_NAMES_FILTERED.SKIP_FLAG_ID Is Null 
      AND t_authors_products.state = 1
    Last edited by pdreyer; 03-23-06 at 04:32.

Posting Permissions

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