try this:

UPDATE available
SET available.date = tarriff.date
WHERE available.TarriffID = tarriff.ID

Error

SQL-query :

UPDATE available SET available.date = tarriff.date WHERE available.TarriffID = tarriff.ID

MySQL said:

Unknown table 'tarriff' in where clause

server is running MySQL 3.23.56

"Before MySQL 4.0.18, you need the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified."

is this the problem although i am logged directly to the DB

Smad

Unknown table 'tarriff' in where clause

This is more likely a misspelled table name, you need to check your table name.

here are my tables

Browse: (35 Rows) available
Browse: (23 Rows) cottages
Browse: (35 Rows) tarriff
Browse: (2 Rows) user

TIA

Smad

I had to look this up myself, but I am sure you can't list two tables in update statment mentioned the way you put it in your first post.

Try something like the following:

UPDATE available
SET available = ( SELECT tarriff.date
FROM tarriff
WHERE available.TarriffID = tarriff.ID)
WHERE EXISTS
( SELECT tarriff.TarriffID
FROM tarriff
WHERE available.TarriffID = tarriff.ID);

I think this should work:

UPDATE available
FROM tarriff
SET available.date = tarriff.date
WHERE available.TarriffID = tarriff.ID

Error

SQL-query :

UPDATE available FROM tarriff SET available.date = tarriff.date WHERE available.TarriffID = tarriff.ID

MySQL said:

You have an error in your SQL syntax near 'FROM tarriff SET available.date = tarriff.date WHERE available.TarriffID = tarri' at line 1

Error

SQL-query :

UPDATE available SET available = ( SELECT tarriff.date
FROM tarriff
WHERE available.TarriffID = tarriff.ID )
WHERE EXISTS (

SELECT tarriff.TarriffID
FROM tarriff
WHERE available.TarriffID = tarriff.ID
)

MySQL said:

You have an error in your SQL syntax near 'SELECT tarriff.date
FROM tarriff
WHERE available.TarriffID = tarriff.ID )
WHERE' at line 1

thanks for all the effort so far

i am using the SQL feature in php myadmin just fyi.

smad

This works in MSSQL, I assume it will work in MySQL

UPDATE available
SET date = (SELECT date FROM tarriff WHERE available.TarriffID = Tarriff.ID)

Excellent thought syber, except that ...

server is running MySQL 3.23.56

... and subquery syntax was not introduced until 4.1.

Starting with MySQL 4.0.4, you can also perform UPDATE operations covering multiple tables

Resource:
[dev.mysql.com...]

You won't be able to do what you want here until you update that MySQL server.

still getting an error..i tried each individual element and its ok, its when 2 tables are involved it gives an error

ah ok coopster..thanks for easing the pain :)

smad

The manual in this section is misleading and therefore confusing. On the one hand,
there is a statement of ...

Starting with MySQL 4.0.4, you can also perform UPDATE operations covering multiple tables.

... and then, on the very same page ...

Before MySQL 4.0.18, you need the UPDATE privilege for all tables used in a multiple-table UPDATE ...

... which is misleading as it leads us to believe that we have the ability to UPDATE multiple tables as of 4.0.18, which is not the case. Here is where we find out for sure, in the changelogs ...

B.2.30. Changes in release 4.0.2 (01 July 2002)

First pre-version of multiple-table UPDATE statement.

Resource:
[dev.mysql.com...]

Either way, you are still running a 3.x version and as you now know, multiple-table updates just were not available at that time. You'll have to program a workaround :(

What am I missing here? Why doesn't he just upgrade, as SQL without subqueries is pretty useless.

thanks all

unfortunately syber the decision to upgrade is with the hosting company not myself. I use a large hosting company in the UK which you would have thought would keep software etc up to date.

smad

the decision to upgrade is with the hosting company not myself

Figured that was the case, therefore leaving you no choice but a programmatic workaround, ... if you remain on the same host. I should have mentioned the alternative option -- to switch hosting companies, letting the current company know why you are doing so. Running technology that is that many years old shows you one thing about the hosting provider you have chosen.

I agree with syber, personally I would upgrade. Even if it means switching hosts.