Problem with a query -- sql server new users

sql server new users : Problem with a query

Christoph
9/12/2006 5:55:47 PM

I have 2 tables. Simplified down to relevant fields.

Table 1

id
referenceName
currentStatus

Table 2

id
table1Id
transactionStatus

There is a many to 1 relationship between table 2 and table 1. Table 1
holds 1 reference record and Table 2 can have multiple records, each having
the status for a particular transaction. I'm trying to update the
Table1.currentStatus field to have the value of the status of the last
associated record in Table 2. For example, if Table 2 had the following
records:

1, 55, fail
2, 55, pass
3, 55, fail
4, 55, fail

the currentStatus for id 55 in table 1 would be 'fail'. If Table 2 had the
following records:

19, 13, pass
20, 13, fail
21, 13, fail
22, 13, pass

the currentStatus for id 13 in table 1 would be 'pass'. Here is the query
that I've come up with but it's not working as expected.

UPDATE Table1
SET Table1.currentStatus =
Table2.transactionStatus FROM
Table2 INNER JOIN Table1 ON
Table1.id = Table2.Table1Id

How can I update table 1 how I need to?

thnx,
Christoph

Tiago Costa
9/13/2006 12:00:00 AM

HI there Christoph,

Just implemets an Insert Trigger in Table 2 that will update the record in
table 1.

Tiago Costa

[quoted text, click to view]

Christoph
9/13/2006 12:00:00 AM

[quoted text, click to view]

This is fine for inserts going forward. But I need to run this query to
update currentStatus in Table 1 with historical data from Table 2. So
unless I copy all the data from Table 2 to another short lived table then
copy it all back (thus utilizing the trigger), I need to come up with a
query that will update all the existing records in Table 1 with the status
in Table 2. That's where I'm at now.

thnx,
Christoph

Tiago Costa
9/13/2006 2:21:42 PM

It's easy:

Develop a Select command with the cluases ORDER BY DESC and TOP 1 to return
de last state of each transaction.
then update the table with the result of the select command.

it should be something like this:

UPDATE Table1
SET currentStatus = (
SELECT TOP 1 transactionStatus
FROM Table2
WHERE table1Id = @idfromtable1
ORDER BY id DESC
)

Tiago Costa

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn