Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: SQL Server Updates Using Joins And Order By

    Hello,

    I'm trying to update a table using joins. The syntax below works until I put an ordered by clause in. I need to make updates in order based on the date field in table B. Is that possible? I know i can do it with a cursor and order the cursor query and then perform updates one by one but, its taking way to long. Has anyone ran into this problem before?

    UPDATE A
    SET A.FIELD1 = B.FIELD1
    FROM TABLE1 A INNER JOIN TABLE2 B ON A.KEY = B.KEY
    ORDER BY B.DATE_FIELD ASC

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to explain what you mean by the idea of the ORDER making a difference.

    ISO Standard SQL does not even permit the use of a JOIN within an UPDATE statement. Transact-SQL implemented JOIN operations within an UPDATE to provide most of the functionality that the MERGE statement provides in ISO SQL (and in SQL 2008), but even they don't implement an ORDER BY clause.

    How does the order matter?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2009
    Posts
    5
    i have a table that contains user information such as
    user id, name, email, addr, city, st, zip

    in another table I get updates for the user table daily with timestamp. Each user can have multiple updates per day. So each update is a new line in a table. In addition not all the fields have to be updated. So one update can have name and email that needs to be changed and the other can have city and st. You can also get a third update where name and email change again.

    So what I'm thinking is if order is not possible maybe use of max statements based on timestamp of the update for each user and based on which fields were updated. Somewhat painful but maybe doable. Its just that update with order by statement was a very easy solution for this as i would just update based on the timestamp order and if the same fields change several times a day then i would always get the last value.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How many total columns are in the table being updated? If there aren't too many of them, you could construct an UPDATE using separate sub-selects for each column to get the most recent update for that column. It would be rather ugly, but I think it would do what you want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    Yeah I would have to do it for 15 columns but, it looks like that might be the only solution in this case, just going column by column and getting the last value.

    Thanks for your help and the quick replies.

    Dang i wish the order by would work in this case

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You could try this:

    WITH B AS
    (SELECT ROW_NUMBER() OVER(PARTITION BY keycol ORDER BY ts DESC) r, *
    FROM TABLE2)
    UPDATE A SET
    col1 = B.col1,
    col2 = B.col2,
    col3 = B.col3
    FROM TABLE1 A, B
    WHERE A.keycol = B.keycol
    AND r = 1 ;

    This seems a rather fragile and inefficient thing to do and I wonder what benefit it has for you rather than using a view to achieve the same thing.

    If you are using SQL Server 2008 then use MERGE instead of UPDATE. MERGE is a much better solution all round.

Posting Permissions

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