Welcome to WebmasterWorld Guest from 54.242.134.77

Forum Moderators: open

Message Too Old, No Replies

my update query problem

please help

     

Smad

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

10+ Year Member



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

phranque

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

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



try this:
UPDATE available
SET available.date = tarriff.date
WHERE available.TarriffID = tarriff.ID

Smad

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

10+ Year Member



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

Habtom

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

WebmasterWorld Senior Member 10+ Year Member



Unknown table 'tarriff' in where clause

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

Smad

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

10+ Year Member




here are my tables

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

TIA

Smad

Habtom

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

WebmasterWorld Senior Member 10+ Year Member



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

dramstore

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

10+ Year Member



I think this should work:

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

Smad

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

10+ Year Member



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

Smad

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

10+ Year Member



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

Smad

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

10+ Year Member



thanks for all the effort so far

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

smad

syber

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

10+ Year Member



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

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

coopster

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

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



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.

Smad

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

10+ Year Member



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

Smad

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

10+ Year Member



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

smad

coopster

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

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



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

syber

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

10+ Year Member



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

Smad

6:28 am on Oct 18, 2007 (gmt 0)

10+ Year Member



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

coopster

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

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



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month