homepage Welcome to WebmasterWorld Guest from 54.243.23.129
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Accredited PayPal World Seller

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
my update query problem
please help
Smad




msg:3477925
 3:55 pm on Oct 15, 2007 (gmt 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

 

phranque




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

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

Smad




msg:3478556
 6:49 am on Oct 16, 2007 (gmt 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

Habtom




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

Unknown table 'tarriff' in where clause

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

Smad




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


here are my tables

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

TIA

Smad

Habtom




msg:3478592
 8:21 am on Oct 16, 2007 (gmt 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);

dramstore




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

I think this should work:

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

Smad




msg:3478595
 8:30 am on Oct 16, 2007 (gmt 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

Smad




msg:3478599
 8:31 am on Oct 16, 2007 (gmt 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

Smad




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

thanks for all the effort so far

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

smad

syber




msg:3479741
 1:05 pm on Oct 17, 2007 (gmt 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)

coopster




msg:3480074
 6:37 pm on Oct 17, 2007 (gmt 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.

Smad




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

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

Smad




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

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

smad

coopster




msg:3480089
 6:55 pm on Oct 17, 2007 (gmt 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 :(

syber




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

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

Smad




msg:3480543
 6:28 am on Oct 18, 2007 (gmt 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

coopster




msg:3480989
 3:55 pm on Oct 18, 2007 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved