Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2017
    Posts
    1

    Unanswered: Update Query For 2 Tables

    Hi All
    i have this Query Below
    string updateReq = "UPDATE Student SET Student.ID_Request = Request.ID_Request FROM Request INNER JOIN Request ON Student.ID_Request = Request.ID_Request";

    but when i excute this i get this error :
    The objects "Request" and "Request" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

    how can i solve this ?

    What I have tried:

    i tried to select the records in the temp table but it doesn't work or maybe i do it wrong
    string select = "SELECT * INTO #TempTable FROM Student s where ID_Request IS NULL";

    string updateReq = "UPDATE TempTable SET s.ID_Request = Request.ID_Request FROM Request INNER JOIN Request ON Student.ID_Request = Request.ID_Request";

    when i excute these string i get the same error

  2. #2
    Join Date
    Jan 2013
    Posts
    359
    Provided Answers: 1

    Your approach is wrong.

    Pretty much everything you're trying to do is wrong. Not a little wrong, but fundamentally, conceptually wrong.

    Tables are sets; that means they have to be collective or plural. You've told us you have only one student one request! I don't believe that is true.

    A join in the ANSI/ISO standard model creates a working table within the scope of the statement in which it appears. This is been true since 1986. So updating this temporary working table does you no good in theory because it will disappear at the end the execution of the statement.

    Things like "_id" are called attribute properties and they appear at the end of the name in the ISO 11179 standards.

    I see we have no idea what the tables look like because you fail to post DDL. Posting DDL has been standard for over 30 years on SQL forums

    >> I have this Query Below <<

    NO! This is a statement, not a query. The differences are very important.

    UPDATE Students
    SET request_id
    = (SELECT R.request_id
    FROM Requests AS R
    WHERE Students.request_id = R.request_id);

    Now if there are multiple requests that match to a student, this will blow up on a cardinality violation. I'd also suggest you look at how this works with the merge statement. Merge will also detect cardinality problems.

    >> What I have tried:

    I tried to select the records [sic] in the temp table but it doesn't work or maybe I do it wrong.
    string select = "SELECT * INTO #TempTable FROM Student s where request_id IS NULL";

    string updateReq = "UPDATE TempTable SET s.request_id = Request.request_id FROM Request INNER JOIN Request ON Student.request_id = Request.request_id";<<

    Why are you doing a self join if you want to update a different table?

Posting Permissions

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