Welcome to WebmasterWorld Guest from 54.160.177.33

Forum Moderators: open

Message Too Old, No Replies

my update query problem

please help

     
3:55 pm on Oct 15, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


hi all

after reading multiple site on updating one column from another i created this.

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

BUT i get this whatever i change.

MySQL said:

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

i am really stuck not knowing much about mysql

TIA

Smad

10:44 pm on Oct 15, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10542
votes: 8


try this:
UPDATE available
SET available.date = tarriff.date
WHERE available.TarriffID = tarriff.ID
6:49 am on Oct 16, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


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

6:54 am on Oct 16, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 0


Unknown table 'tarriff' in where clause

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

7:57 am on Oct 16, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0



here are my tables

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

TIA

Smad

8:21 am on Oct 16, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 0


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);

8:26 am on Oct 16, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 23, 2003
posts:76
votes: 0


I think this should work:

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

8:30 am on Oct 16, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


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

8:31 am on Oct 16, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


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

8:33 am on Oct 16, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


thanks for all the effort so far

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

smad

1:05 pm on Oct 17, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


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

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

6:37 pm on Oct 17, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.

6:38 pm on Oct 17, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


still getting an error..i tried each individual element and its ok, its when 2 tables are involved it gives an error
6:39 pm on Oct 17, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


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

smad

6:55 pm on Oct 17, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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 :(

2:40 am on Oct 18, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


What am I missing here? Why doesn't he just upgrade, as SQL without subqueries is pretty useless.
6:28 am on Oct 18, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 29, 2003
posts: 92
votes: 0


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

3:55 pm on Oct 18, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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.