Database updates
-
Hi, I want to update the column TID with the top most value of TID as long as MID is the same number. I want to do this (table after update) PID SID TID MID RID 3 0 0 4 1 3 NULL 0 4 2 3 NULL 0 4 3 3 NULL 0 4 4 3 NULL 0 4 5 3 NULL 0 4 6 3 NULL 0 4 7 3 4 4 8 1 3 NULL 4 8 2 3 NULL 4 8 3 3 NULL 4 8 4 Below is the actual table before update. PID SID TID MID RID 3 0 0 4 1 3 NULL NULL 4 2 3 NULL NULL 4 3 3 NULL NULL 4 4 3 NULL NULL 4 5 3 NULL NULL 4 6 3 NULL NULL 4 7 3 4 4 8 1 3 NULL NULL 8 2 3 NULL NULL 8 3 3 NULL NULL 8 4 Any suggestion/solutions deeply appreciated. Thanks
-
Hi, I want to update the column TID with the top most value of TID as long as MID is the same number. I want to do this (table after update) PID SID TID MID RID 3 0 0 4 1 3 NULL 0 4 2 3 NULL 0 4 3 3 NULL 0 4 4 3 NULL 0 4 5 3 NULL 0 4 6 3 NULL 0 4 7 3 4 4 8 1 3 NULL 4 8 2 3 NULL 4 8 3 3 NULL 4 8 4 Below is the actual table before update. PID SID TID MID RID 3 0 0 4 1 3 NULL NULL 4 2 3 NULL NULL 4 3 3 NULL NULL 4 4 3 NULL NULL 4 5 3 NULL NULL 4 6 3 NULL NULL 4 7 3 4 4 8 1 3 NULL NULL 8 2 3 NULL NULL 8 3 3 NULL NULL 8 4 Any suggestion/solutions deeply appreciated. Thanks
-
Assuming your DB is SQL Server, you can try this (just out of mind, not tried though)
UPDATE MyTable A SET A.TID = (SELECT MAX(B.TID) FROM MyTable B WHERE A.MID = B.MID)
hi thanks for your reply. But am afraid your SQL didnt work. All it did was updated the entire column with the max value in the TID column.
-
Assuming your DB is SQL Server, you can try this (just out of mind, not tried though)
UPDATE MyTable A SET A.TID = (SELECT MAX(B.TID) FROM MyTable B WHERE A.MID = B.MID)
Thanks for your help. I have solved it by creating a view of all 'Not NULL' (top most) rows . Then created a stored proc to loop through the view and update the TID. A long procedure though but it did work. Cheers !
-
Hi, I want to update the column TID with the top most value of TID as long as MID is the same number. I want to do this (table after update) PID SID TID MID RID 3 0 0 4 1 3 NULL 0 4 2 3 NULL 0 4 3 3 NULL 0 4 4 3 NULL 0 4 5 3 NULL 0 4 6 3 NULL 0 4 7 3 4 4 8 1 3 NULL 4 8 2 3 NULL 4 8 3 3 NULL 4 8 4 Below is the actual table before update. PID SID TID MID RID 3 0 0 4 1 3 NULL NULL 4 2 3 NULL NULL 4 3 3 NULL NULL 4 4 3 NULL NULL 4 5 3 NULL NULL 4 6 3 NULL NULL 4 7 3 4 4 8 1 3 NULL NULL 8 2 3 NULL NULL 8 3 3 NULL NULL 8 4 Any suggestion/solutions deeply appreciated. Thanks
I think you want to do something like this ...
update table A
set tid =
(select max(tid)
from TableA
Group By mid)Check these for references: http://msdn.microsoft.com/en-us/library/ms177523.aspx http://data.bangtech.com/sql/sql\_update\_with\_correlated\_subquery.htm
-
I think you want to do something like this ...
update table A
set tid =
(select max(tid)
from TableA
Group By mid)Check these for references: http://msdn.microsoft.com/en-us/library/ms177523.aspx http://data.bangtech.com/sql/sql\_update\_with\_correlated\_subquery.htm
Hi thanks for your solution and links, But your query returned an error. Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
-
Hi thanks for your solution and links, But your query returned an error. Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
That's because he forgot a small but important condition. Try this:
update table A as a1
set tid =
(select max(tid)
from TableA as A2
Where A1.mid = A2.mid
Group By mid)Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
That's because he forgot a small but important condition. Try this:
update table A as a1
set tid =
(select max(tid)
from TableA as A2
Where A1.mid = A2.mid
Group By mid)Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.
-
Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.
David Mujica wrote:
Sometimes when you don't have the actual data to test with, you forget some stuff.
Tell me about it. :rolleyes: That's almost something I can put in my CV.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions