update query (multiple tables)

Anon

Hi everyone, (anyone)

I am trying to update values of a column in one table with corresponding values in another similar table.

I understand that MySQL does not allow update queries where more than one table is involved.

Now I really miss that. Any suggestions on workarounds?

I've tried running the query from Access via ODBC (MS Access SQL does provide such a syntax) but then I get a record lock error time and again.

I'll be very grateful if anyone can help.

Wim

Anon

hi wim,

i think you're talking about a transaction. this means you have to ensure, that an update on the second table only get's finished if the first table was successful. unfortunaly mysql doesn't support transactions. you would have to use php (for example)to build an workaround.

if you want to explain the problem a little bit more in detail - i perhaps could discuss a solution according your problem.

have fun.

chs

Anon

hi Christian,

Thanks for the quick offer to help. No I am not talking about transactions. I only want to update one table. I am taking up your invitation for an exact explaination of what I'm trying to do:

The one table is called FIXTURES_LOCAL and has FIX_ID_NUMBER as primary key, and the other is FIXTURES, also with FIX_ID_NUMBER as key. Both have got a field called PLANNED_DATE. The PLANNED_DATE of FIXTURES_LOCAL is the value I trust and prefer over the value in FIXTURES. So from time to time I want to update existing records' PLANNED_DATE's in FIXTURES to the PLANNED_DATE in FIXTURES_LOCAL.

MySQL doesn't have a JOIN clause for update queries. It is such a workaround that I'm seeking a solution for. The programming workaround that I can think of is going to be a lot of pt to achieve a simple task. It will mean loading FIXTURES record by record, get the corresponding record in FIXTURES_LOCAL, and setting FIXTURES.PLANNED_DATE to FIXTURES_LOCAL.PLANNED_DATE.

As far as I know one cannot update the result set of a select query in MySQL.(?) That would also have solved my problem.

thanks again

Wim

Anon

hi wim,

i also can't see a solution to your problem just using one single sql-statement.

i would suggest you do a joined-select-statement to get all updateable rows and then stepping through the result to update every single row.

greetings from baden-baden / germany.

christian

Anon

hi Christian,

(scroll down for English version - I just had to try out my rusty foreign language skills)

Dein letzten Idee ist genau was ich machen werde. Kein Problemen fuer mich, mehrere Querien zu brauchen. Ich habe fuer diese Problem aber eine andere (vielleicht dumme) Frage dabei. Gibt es denn ein Weg womit man nur SQL brauchen kann, durch die Results einer Select Query zu gehen (nicht mit php, ado usw) ?

Vielendank fuer Deine Hilfe.

Gruss aus Johannesburg, Suedafrika.

Yes, your last idea should help. I don't mind at all using more than one query. Yet for this problem it brings me to another (probably stupid) question. Is there a way of updating a result set of a select query by just using SQL (not stepping through with php / ado etc)?

Thank you so much for your help.

Greetings from Johannesburg, South Africa

Anon

hi wim,

just read your answer - your german is very good! you suprised me !

your question isn't stupid - i had the same idea and was already looking for a solution.

unfortunaly i couldn't find an answer yet.

the sql query (i have in my mind):

update tablename set field=value where (select value from anothertablename where condition=something)

perhaps you want to try it out - i'm not sure it works...

greetings!
christian