Page 1 of 1

Update one table from the first row of another

Posted: Fri May 11, 2007 1:48 pm
by Burrito
I'm using this SQL (this is for MSSQL NOT MySQL)

Code: Select all

UPDATE    aaatest
SET              aaatest.test2 = aa.test2, aaatest.test3 = aa.test3
FROM         aatest AS aa INNER JOIN
                      aaatest ON aa.test1 = aaatest.test1
WHERE     aa.test2 != aaatest.test2 OR
                      aa.test3 != aaatest.test3
that works fine and dandy, but it updates aaatest with the last row that it finds in aatest and I need it to update with the first row.

can anyone provide some guidance?

thx,

Burr

Posted: Fri May 11, 2007 1:49 pm
by Weirdan
hmm... what about a subselect?

Posted: Fri May 11, 2007 1:59 pm
by Burrito
thought about that, but I'm actually going to be updating several thousand rows.

Let me elaborate on the problem a bit.

I have a temp table that might have multiple rows for each student. The first row (for each student) is the one that has ALL of the information I need, the other rows only have partial info. The 'real' table only has one row per student and it is the one that *potentially* needs to be updated. I say potentially because, if the data hasn't changed, it shouldn't be updated, that's why I'm doing it this way rather than a sub select (I need to check both tables to see if there's a difference). The first run-through should find a LOT of differences as a LOT of the data in the 'real' table is missing due to the fact that it hasn't been working properly and has been updating with the last row found vs the first row found.

If I did try the subselect route, is it possible to check against the real table the values from the temp table?

Posted: Fri May 11, 2007 2:02 pm
by RobertGonzalez
I think you can set the diff checks in the WHERE of the update. So you would update the row only where the selected data is not the same as the data that needs to be updated.

In my head this makes perfect sense but practically, I have no way to test this.

Posted: Fri May 11, 2007 2:27 pm
by Weirdan
The first row (for each student) is the one that has ALL of the information I need
Do you have a field in your temp table which can be used to see if the particular row is the first for the student it's related to? Something you could put in ORDER BY clause if you were just selecting from the temp table...

Posted: Fri May 11, 2007 2:52 pm
by Burrito
I think I'm just going to use a bunch of subqueries for all my fields and not worry about whether the values match or not.

ex:

Code: Select all

UPDATE    aaatest
SET              test2 =
                          (SELECT     TOP 1 aa.test2
                            FROM          aatest AS aa
                            WHERE      aaatest.test1 = aa.test1
                            ORDER BY aa.id), test3 =
                          (SELECT     TOP 1 aa.test3
                            FROM          aatest AS aa
                            WHERE      aaatest.test1 = aa.test1
                            ORDER BY aa.id)