I have a lookup table that holds two columns - a key value and another
column containing items. For e.g.
Col1 Col2
1 10
1 15
1 12
2 20
2 15
Anohter table has to be updated with col1 and the max value of col2
for every col1. So ti should look like
1 15
2 20
....
Right now i have a sQL statement that gets the max of the first table
and then does an update on the second. I was thinking about making it
a single query. Multi table update doesnt work here as the order of
updates is not guaranteed.
So i wrote a UDF that will get the maximum value of a column for any
table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The
maximum value of col2 in Table1 where col1 = 1).
When i use this in my update
update table2 set column = maxfunc('Tab1', 'Col2', 1)
where column2 = 1;
hangs mysql.
Has anyone tried UDF with update statements?
TIA
Raja
