Update one table from the first row of another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Update one table from the first row of another

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

hmm... what about a subselect?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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...
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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)
Post Reply