Results 1 to 11 of 11
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: syntax error in stored procedure

    Hi,

    I need to write a stored procedure takes the first row of the record found in tblshipping_sched and update the second table tblBag_data.
    But I'm have trouble with the syntax.
    Error message
    Incorrect syntax near '.' on the last line of the sp



    UPDATE dbo.tblBag_data
    SET dbo.tblBag_data .cust_num = dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched INNER JOIN (SELECT TOP 1 dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched) dbo.tblBag_data ON dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num

    Thanks very much!

  2. #2
    Join Date
    May 2002
    Posts
    395

    Re: syntax error in stored procedure

    Originally posted by snail
    UPDATE dbo.tblBag_data
    SET cust_num = dbo.tblShipping_sched.cust_num -- just remove table
    FROM dbo.tblShipping_sched INNER JOIN (SELECT TOP 1 dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched) dbo.tblBag_data ON dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num
    Still the syntax occurred on the same line : dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num

    hm..??

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: syntax error in stored procedure

    Originally posted by alicejwz
    Still the syntax occurred on the same line : dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num

    hm..??
    You have the same name for alias and for table - I guess it is a problem.
    You are trying to update table dbo.tblBag_data but by alias it is table dbo.tblShipping_sched. Something is wrong with logic in your query.

  4. #4
    Join Date
    May 2002
    Posts
    395

    Re: syntax error in stored procedure

    Originally posted by snail
    You have the same name for alias and for table - I guess it is a problem.
    You are trying to update table dbo.tblBag_data but by alias it is table dbo.tblShipping_sched. Something is wrong with logic in your query.
    I guess that's what I don't understand. Why is dbo.tblShipping_sched being references as an alias? I'm joining the two tables by work_ord_num. Then I want the first record that matches in tblShipping_sched to return as the recordset that updates tblBag_data.

    I notice alias is used in referencing a table. Why?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    On the second line of the query, is that a space between dbo.tblBag_data and .cust_num?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: syntax error in stored procedure

    Originally posted by alicejwz
    I need to write a stored procedure takes the first row of the record found in tblshipping_sched and update the second table tblBag_data.
    The problem lies here.

    There is no meaning to first row, second row.

    The order of data in a database is irrelevant...

    It's gotta be based on keys (which I see in your ON clause, I think)

    What are you really trying to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    alias cannot have a schema ownership reference. if you need to update just the first row in tblBag_data, then set rowcount 1 before the update statement and get rid of select top 1.

  8. #8
    Join Date
    Aug 2003
    Location
    Chennai, India
    Posts
    3

    Re: syntax error in stored procedure

    Originally posted by snail
    UPDATE dbo.tblBag_data
    SET cust_num = dbo.tblShipping_sched.cust_num -- just remove table
    FROM dbo.tblShipping_sched INNER JOIN (SELECT TOP 1 dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched) dbo.tblBag_data ON dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num
    Hi,

    Did U people check the virtual table U have a column called cust_num and U want it to be verified with work_ord_num column
    so the query shld be like
    UPDATE dbo.tblBag_data
    SET cust_num = dbo.tblShipping_sched.cust_num FROM dbo.tblShipping_sched INNER JOIN (SELECT TOP 1 *
    FROM dbo.tblShipping_sched) dbo.tblBag_data ON dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num

    Check this out and revert.

    Thanks & Regards,
    Pasha S...

  9. #9
    Join Date
    Dec 2003
    Location
    Hyderabad
    Posts
    3

    Re: syntax error in stored procedure

    (SELECT TOP 1 dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched) dbo.tblBag_data

    This is not a valid query in your entire query. You write a qurery for selecting the first row and use second table name as alias.

  10. #10
    Join Date
    Aug 2003
    Location
    Chennai, India
    Posts
    3

    Re: syntax error in stored procedure

    Originally posted by kishorekk
    (SELECT TOP 1 dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched) dbo.tblBag_data

    This is not a valid query in your entire query. You write a qurery for selecting the first row and use second table name as alias.
    Yes, Kishore is right, kindly check it out.
    Rgds,
    Pasha S...

  11. #11
    Join Date
    Dec 2003
    Location
    Hyderabad
    Posts
    3

    try With this query

    UPDATE dbo.tblBag_data
    SET dbo.tblBag_data .cust_num = dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched WHERE dbo.tblShipping_sched.cust_num=(SELECT TOP 1 dbo.tblShipping_sched.cust_num
    FROM dbo.tblShipping_sched JOIN dbo.tblBag_data ON dbo.tblBag_data.work_ord_num = dbo.tblShipping_sched.work_ord_num)

Posting Permissions

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