January 5, 2007 at 7:45 am
Hi i like to understand how to do a update statement where i have a select statement with a max and where cluase. Because i've a max i would also need a group by. Would i be on the right track with this ?
Update tbl_1
set ind = '1'
From ( select col_1, max (col_2)
from tbl_1
where col_3 = ' abc'
group by col_1,col_2 )
January 5, 2007 at 7:56 am
Please explain in words what the update should do. I don't understand what is your requirement.
If you simply want to set column ind to '1' in all rows (according to WHERE), then what do you need the MAX(col2) for???
Your query wouldn' work, there are some syntax errors, but since I'm not sure what you are doing, I can't help you directly. Generally, you can write similar queries, you just need to name the table to be updated, and join to it - like that:
UPDATE tbl_1
SET salesum = Q.mysum
FROM tbl_1
JOIN
(select ID_col, sum(sales) as mysum
from tbl_1
where col_3 = ' abc'
group by ID_col) as Q
ON Q.ID_col = tbl_1.ID_col
January 5, 2007 at 8:08 am
Hi Vladan,
Am working off the same table in this case. My problem is that i have duplicate logs with different sequence numbers and datetime so i use the max to get the max sequence and date time where the code = 2
I only want to set 1 as the indactor on these logs and show the distinct list of them.
January 5, 2007 at 8:44 am
I was working with one table, too, in my example. Hmmm.. well, do you mean that you want to mark with 1 only those rows that have max sequence number? OK, then it could be this way:
UPDATE a
SET ind = '1'
FROM tbl_1 a
JOIN
(select col_1, max(col_2) as maxc2
from tbl_1
where col_3 = ' abc'
group by col_1) as Q
ON Q.col_1 = a.col_1 AND a.col_2 = Q.maxc2
Derived table Q finds maximum value of col2 for each value in col1, and the UPDATE sets ind to 1 on all rows where value of col2 is equal to this maximum for the given value of col1.
Is this what you need?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply