sql query to update field from from one table to another?

Question

Can someone tell me what's wrong with this sql query syntax?

update tableA
set tableA.field1 = tableB.field2
from tableA, tableB
where tableA.id = tableB.id

mysql is complaining syntax issue near from tableA

thanks!

Answer 1

Try something like this:



update tableA set field1 = (select tableB.field2 from tableA, tableB where tableA.id=tableB.id)



edit: It looks like the subquery syntax wasn't introduced until 4.1. Starting with mysql 4.0.4, you can use multi-table update syntax -



update tableA, tableB set tableA.field1=tableB.field2 where tableA.id=tableB.id



If you have an earlier version, you'll probably need to do 2 separate queries.



See the update documentation below.

Answer 2

I am not sure the logic works that way. You may have to select the value you want from table b then run a second query doing the update with the value that you got from the first query.

Answer 3

you should attempt this interactively with the help of only a million) INSERT INTO subject matter FROM (elect volume + rowCount, identify, essay FROM subject matter, (elect count number(*) AS rowCount FROM subject matter)) 2) elect MAX(volume) FROM subject matter 3) If outcome in 2 < 100, pass to at least a million and repeat 4) DELETE FROM subject matter the position volume > 100 a thanks to attempt this non-interactively is with the help of a script (T-sq., PL/sq. or in spite of is perfect on your DBMS) with sq. embedded in it. in case you recognize the present rows are contiguous and commence at a million (as on your celebration) a million) Get maxValue wanted from person (100 hence) 2) elect count number(*) FROM subject matter INTO curRowCount 3) numInserts = 0, objective = maxValue 4) till objective = 0; a) numInserts = numInserts + a million b) objective = objective / curRowCount 5) For i = a million->numInserts a) INSERT INTO subject matter FROM (elect volume + curRowCount, identify, essay) b) curRowCount = curRowCount * 2 6) DELETE FROM subject matter the position volume > maxValue

Answer 4

Try:



update a

set a.field1 = b.field2

from tableA a inner join tableB b on

a.id = b.id



You dont need a subquery

Answer 5

update tableA

set tableA.field1 = (select tableB.field2

from tableA, tableB

where tableA.id = tableB.id)



You actually need a sub-select......you were close