How to UPDATE column value off another column value..

[Scooby08]

Using MySql

 

How in the world would a table like this:

 

id - new_id - result - final_result

0    1            win      NULL

1    1            loss    NULL

2    2            win      NULL

3    2            win      NULL

 

be updated into a table like this:

 

id - new_id - result - final_result

0    1            win      loss

1    1            loss    loss

2    2            win      win

3    2            win      win

 

Am just trying to check if any group of new_id's has a result value of loss.. if so, update all fields in that group of new_id's to loss, else win..

 

Thanks!!

 

[Altrozero]

In this case you can use something like this. Not exactly fast but a simple way of doing it in just msql

 

UPDATE results, (SELECT id, (SELECT tb2.result FROM results as tb2 WHERE tb2.new_id = tb1.new_id ORDER BY result ASC LIMIT 1) as result FROM results as tb1) as tb3 SET results.final_result = tb3.result WHERE results.id = tb3.id

[Scooby08]

Hello Altrozero..

 

I just wanted to let you know that your suggestion did work out excellently! Thanks

 

I did notice that you said this query is not exactly very fast.. How many records at a time do you think this query could handle without getting too bogged down? (Ball park)

 

If you, or anybody else reading this, wouldn't mind throwing out some suggestions as to how to speed this up I'd really be interested in that..

 

Thanks again!