catch 22 query
-
I have a problem whereby I need to update a table using set based sql.
The update gets its info in the form of a derived table in the from section.
What I need to do in the derived table is only pull back one matching row that has a max value. So i would have something like
update a set a.a=b.a from (select top 1 accno, seq from acc order by seq desc) b join a aa on (aa.accno=b.accno) where a.accno=b.accno
the problem i have is that i want to use top in the derived table and sql server won't let me
any ideas ?
cheers
dbgeezer
-
How about this?
update a set a.a=b.a from acc b join a aa on (aa.accno=b.accno) where a.accno=b.accno and (acc.seq =
(SELECT MAX(seq)
FROM acc c
WHERE b.accno = c.accno))
-
indeed but the problem is that i have several other values to pull from the row with the max seq.
what i seem to be getting is a mix of the set of values of which max(seq) is at the top
so, what i really need is to identify the top row, where max(seq) sits and apply all the values i select to update the corresponding accno in the original table.
i can't post the query or the tables for business reasons
cheers
dbgeezer
-
Difficult to answer due to awkward table names but is this what your're after
update a
set a.a = b.a, a.b = b.b, a.c = b.c
from (select max(seq) as [seq] from acc) m
inner join acc b on b.seq = m.seq
inner join originaltable a on a.accno = b.accno
Far away is close at hand in the images of elsewhere.
Anon. -
okay - this is the query i have
had to remove the query - sorry
your suggestion to add the max(Sequence) (bold) gives the following error
"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
cheers
dbgeezer
-
How about this
(Removed at posters request)
Far away is close at hand in the images of elsewhere.
Anon. -
hmmm that one gave me a few errors - i did however fix it in the meantime with the following. the key was the inner select and then the ugly addition to the group by.
had to anonymise the query at the request of my boss.
the problem was solved by having the inner select to qualify the max sequence and then placing that in a having clause.
the inner select to qualify the max sequence was the key - many thanks to all
cheers
dbgeezer
-
Sorry, errors in my post, should be
update a
set a.b = ah.b,
a.seq = m.maxseq,
a.c = ah.c,
a.d = ah.d
from tablea a
inner join (select id, max([seq]) as maxseq
from tableb
group by id) m
on m.id = a.id
inner join tableb b
on b.id = a.id
and b.seq = m.maxseq
changed at posters request to remove sensistive info
btw, dbgeezer, you could have posted some ddl that did not match your database names but would have given us something to work on. It is difficult enough to find a solution but it is frustrating when you do and the poster says it is not quite right and then gives all the important details missing in the first post. I accept that certain things are private and confidential (although I think your boss went over the top in this case as the code you posted gave away nothing) but it is possible to generate ddl (even to hide your db design and naming) to aid in finding a solution.
This is not a criticism.
It can be disappointing to get a square when you really wanted a circle.
Far away is close at hand in the images of elsewhere.
Anon. -
Hi Dave,
could you edit your posts please to remove the query. my boss told me he doesn't want any of the structure of the database on newsgroups.
thanks
cheers
dbgeezer
-
Point taken. Sorry about the messing about but I didn't have time to add any ddl. In future I'll make sure I post enough details.
cheers
dbgeezer
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply