Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    Unanswered: Table Names and Field Names

    I'm trying to do an update query that looks like this:

    UPDATE

    PAEMPLOYEE

    SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL


    FROM

    PAEMPLOYEE A

    JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE

    It's erroring out on the Employee prefix B.EMPLOYEE saying:

    ..."does not match with a table name or alias name used in the query"


    Is it wrong or will it cause problems to have a field name the same as the table name?
    Last edited by AnSQLQuery; 01-21-04 at 18:37.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ???

    I don't have QA in front of me but it looks like the problem is not in the B.EMPLOYEE prefix, but perhaps in that you are trying to update PAEMPLOYEE, but you have aliased it as "A". To simplify things, try eliminating the aliases altogether. One thing that always annoys me when I have to debug or modify someone elses code is when informative object names are aliassed with enigmatic and meaningless names like "A" or "B", just to save some typeing. Generally, the confusion and increased potential for error far outweighs the time it would take to type the full object names.

    UPDATE PAEMPLOYEE
    SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
    FROM PAEMPLOYEE
    INNER JOIN EMPLOYEE ON PAEMPLOYEE.EMPLOYEE = EMPLOYEE.EMPLOYEE
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    thanks

    Thanks for that reply. The reason I use the alias is because I just thought that was the "cool" thing to do. It really makes no difference to me. I'm still noobish, so forgive me. Anyway, this is what ended up working:

    UPDATE ownername.PAEMPLOYEE

    SET LOCAT_CODE = ( SELECT ownername.EMPLOYEE.PROCESS_LEVEL

    FROM ownername.EMPLOYEE

    WHERE ownername.EMPLOYEE.EMPLOYEE = ownername.PAEMPLOYEE.EMPLOYEE)

    WHERE EXISTS
    ( SELECT ownername.EMPLOYEE.PROCESS_LEVEL
    FROM ownername.EMPLOYEE
    WHERE ownername.EMPLOYEE.EMPLOYEE = ownername.PAEMPLOYEE.EMPLOYEE);


    I tried it the way you wrote it, and it just wouldn't work. I did it this way, and it worked just fine. I appreciate your reply, and your advice. Also, I am still not sure why SQL Server Requires me to put in the ownername, and not just the table name. THis server happens to be 7.0, and I know 2000 doesn't make me do that.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whether the owner name is required depends on who owns the object. When you reference an object without an owner name SQL server first looks for an object with that name that is owned by you. If it can't find one, it looks for one that is owned by "dbo" (database owner). If it still can't find one, it stops looking. It does not automatically look for identically named tables owned by other logins. If it did, and it found more than one, how would it decide which to use? Of course, your login has to have permission for those tables as well.

    I'm not sure why the code I posted didn't work, except that I wrote it off the top of my head without testing. I'll try it when I get to work tomorrow. Did it give you any error message?

    One obvious sign of a rookie developer (again in MOO) is overuse of aliases. Another is joining tables in WHERE clauses... When you write code, think more about how quickly you'll be able to debug it six months from now than about how quickly you can type it right now. Aim for clarity, organization, and consistency. That's cool. Blindman cool
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    ok, i lied to you. I didn't do the Inner Join like you did. I did the rookie where clause thing. Well, at least it worked. That makes perfect sense about the owner(s). I really should have figured that out. Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So here it is with an inner join.

    Update PAEMPLOYEE
    set LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
    from ownername.PAEMPLOYEE PAEMPLOYEE
    inner join ownername.EMPLOYEE EMPLOYEE
    on PAEMPLOYEE.EMPLOYEE = EMPLOYEE.EMPLOYEE
    and EMPLOYEE.PROCESS_LEVEL is not null

    Notice how I DID use aliases to keep from repeating the ownername over and over again. Repeating the ownername doesn't really increase the readability of the code, and using the alias in this case allows you to change the ownership of a table without have to change all the references to it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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