Results 1 to 3 of 3

Thread: No Inner Joins?

  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Unanswered: No Inner Joins?

    Hello:

    I'm relatively new to MSQL - I come from a MySQL background and have a crap load of experience with Access/Jet. I tried executing a query in MSQL last night, and got an error. Here's my query:

    update inv_mast inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid SET inv_mast.short_code = 'Entrelec' WHERE inv_loc.primary_supplier_id = '100086'

    I got the error:

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'inner'.

    So I tried changing the syntax to:

    UPDATE inv_mast
    SET inv_mast.short_code = 'Entrelec'
    FROM inv_mast, inv_loc
    WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
    AND inv_loc.primary_supplier_id = '100086'

    And didn't get an error, but I didn't get any results (0 row(s) affected).

    What am I doing wrong?

    If my queries don't give enough of an explanation of the layout of the data, let me know, I'll gladly explain more.

    Thanks all!

    -jim

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    your update statment probably should have looked like:
    Code:
    update inv_mast 
       SET inv_mast.short_code = 'Entrelec'
      from inv_mast
      inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid 
     WHERE inv_loc.primary_supplier_id = '100086'
    and if you like aliases:
    Code:
    update im 
       SET im.short_code = 'Entrelec'
      from inv_mast im
      join inv_loc  il on im.inv_mast_uid = il.inv_mast_uid 
     WHERE il.primary_supplier_id = '100086'
    as to why you didn't update anything, maybe your keys don't line up or you have nothing that satisfies your where clause. what does the following produce?
    Code:
    select im.inv_mast_uid,il.primary_supplier_id
      from inv_mast im
      join inv_loc  il on im.inv_mast_uid = il.inv_mast_uid 
     order by 1,2
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Posts
    4
    Paul,

    Thanks a TON for your response! The last query you posted lists all the item master ID's with their vendor IDs. So I guess the keys are good.

    I'm going to try your update query now...

    Dude - I owe you big time!! It worked like a charm - how can I thank you?

    -jim

Posting Permissions

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