MS Access UPDATE dosn't work in MySQL

[sgivoni]

When I was porting Access to MySQL I got a lot of problems specially with UPDATE. I have been reading a lot of threads but I can't find the answer.
Why do I get an error when using:

UPDATE Hjemmeside , Medlem SET Hjemmeside.SidsteLogin='2003-06-01' WHERE Medlem.MedlemsID=Hjemmeside.MedlemsID AND Medlemsnr=1

Error-message:

You have an error in your SQL syntax near ' Medlem SET Hjemmeside.SidsteLogin='2003-06-01' WHERE Medlem.MedlemsID=Hjemmesid' at line 1

Can it be related to the version of MySQL 3.23.54 ???

Please help me, I'm desperate!!!!!

 

[ToeShot]

It is a format isuue between the two on the SQL string


UPDATE [red]<Table Name>[/red] SET [red]<Field Name>[/red]='2003-06-01' WHERE [red]<Field Name>[/red]=[red]'<Criteria>'[/red] AND [red]<Field Name>[/red]=[red]'<Critera>'[/red]

if it is a date field you may need to do the #2003-06-01#

Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[sgivoni]

The Date field is not the problem - I've testet with simple UPDATE <Table Name> SET <Field Name>='2003-06-01' and all records was updatet.
It seems as if I use more than one tablename after UPDATE(seperatet by , ) it dosn't work, and if I don't I get an error in my criterias.

 

[r937]

yes, it can be related to the version

acording to the mysql docs,

Starting with MySQL Version 4.0.4,
you can also perform UPDATE operations
that cover multiple tables:

UPDATE items,month
SET items.price=month.price
WHERE items.id=month.id;

Note: you can not use ORDER BY or LIMIT
with multi-table UPDATE.



rudy

http://r937.com/

 

[sgivoni]

Hi r937

So how would I do it in a earlier version

Thanks for your help so far!