MySQL updating many records and columns in a table using a UPDATE and a SELECT

Question

I've added the last three fields to this table, but they are blank. All of the records have the top three filled in. The table definition is,

mysql> describe nh3tk;

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| spectra         | varchar(50) | NO   | PRI | NULL    |       |
| value           | float       | NO   |     | NULL    |       |
| error           | float       | NO   |     | NULL    |       |
| nonthermal_FWHM | double      | YES  |     | NULL    |       |
| sound_speed     | double      | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

The Select query, which works is,

select nh3tk.spectra, 
  pow((pow(2.35*nh3lw.sd_lw,2)-1.38065e-23*8*ln(2)*nh3tk.value/2.82e-26),0.5) as nonthermal_FWHM,
  pow(1.38065e-23*nh3tk.value/(2.33*3.348e-27),0.5) as sound_speed 
from nh3tk 
 left join nh3lw on nh3tk.spectra=nh3lw.spectra;

Now I just need to take the two calculated fields from that query and put them in the right fields in nh3tk, where the spectra match.

Any help would be gratefully appreciated.

Best regards James

Quassnoi · Accepted Answer · 2011-01-20T19:42:41.387

2

UPDATE  nh3tk
JOIN    nh3lw
ON      nh3lw.spectra = nh3tk.spectra
SET     nonthermal_FWHM = pow((pow(2.35*nh3lw.sd_lw,2)-1.38065e-23*8*ln(2)*nh3tk.value/2.82e-26),0.5),
        sound_speed = pow(1.38065e-23*nh3tk.value/(2.33*3.348e-27),0.5)

Not sure about the error, since you don't select it in your query.

edited Jan 20 '11 at 19:42

answered Jan 18 '11 at 18:05

Quassnoi

413,100
91
616
614

Sorry but this doesn't work. I get the following error,ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN nh3lw ON nh3lw.spectra = nh3tk.spectra' at line 4 – James Jan 20 '11 at 17:37
Different brands of sql use different UPDATE syntax, I'm prolly wrong, but it looks like Quassnoi is using MSSQL. – Parris Varney Jan 20 '11 at 18:08
@James: sorry, messed the clause order. Please try now. – Quassnoi Jan 20 '11 at 19:42

score 0 · Answer 2 · edited Jan 20 '11 at 20:41

0

I ended up using this to do this task in the end,

UPDATE  nh3tk 
LEFT JOIN nh3lw ON nh3lw.spectra = nh3tk.spectra 
SET nonthermal_FWHM = pow((pow(2.35*nh3lw.sd_lw,2)-1.38065e-23*8*ln(2)*nh3tk.value/2.82e-26),0.5),         
    sound_speed = pow(1.38065e-23*nh3tk.value/(2.33*3.348e-27),0.5);

Thanks all for your answers and taking the time to look at this, James

edited Jan 20 '11 at 20:41

Dan J

16,319
7
50
82

answered Jan 20 '11 at 18:04

James

1,764
5
31
49

MySQL updating many records and columns in a table using a UPDATE and a SELECT

2 Answers2