Multiple Update

[UKmichael]

Hi all

I have two simple tables t1 and t2. I want to update the price field in t2 with the values of the (selected) price field in t1. I have followed other threads regarding similar problems and have worked out that these two attempts should work. Alas, they don't. Anybody any ideas?
Thanks.

UPDATE t2, t1
SET t2.price = t1.price
WHERE t1.item_code=t2.item_code
AND (((t1.selected)= 1))

UPDATE t2 INNER JOIN t1 ON t1.item_code=t2.item_code
SET t2.price = t1.price
WHERE (((t1.selected)= 1))

I am pasting these directly into mysql using phpmyadmin.

 

[BNPMike]

The second one looks fine. What happens - anything?

 

[UKmichael]

Thanks for the response. I just get this error message;

You have an error in your SQL syntax near 'INNER JOIN t1 ON t1.item_code=t2.item_code
SET t2.price = t1.price

 

[TonyGroves]

The second query won't work because the syntax is not legal, but the first should.

What happens when you try the first query? Is the query rejected (what error message appears?), or do you get the wrong results?

 

[UKmichael]

Get this error message when I run the first query;

You have an error in your SQL syntax near ' t1
SET t2.price = t1.price
WHERE t1.item_code=t2.item_code

 

[BNPMike]

Tony

When you say it's not legal, it is SQL legal but appears not acceptable to MySQL. Is that what you're saying?

 

[TonyGroves]

BNPMike -

I'm sorry, I eat my words.

The UPDATE ... INNER JOIN syntax does not appear in the MySQL syntax reference, neither in the manual, nor in P. DuBois's book. However, it does seem to work, I'm just after testing it.

I wonder is UPDATE ... OUTER JOIN legal? I would certainly have a use for it myself.

 

[UKmichael]

If these examples don't work, has anybody else got examples of code that does work relating to the multiple update?

I don't understand why there seems to be such a problem with what other database systems do without effort ie Access. It would be good to have a working example for everybody else to be able to use.

 

[TonyGroves]

UKMichael -

It appears multiple-table updates weren't introduced into MySQL until version 4.0.2. Are you running an earlier version?

 

[UKmichael]

Tony

I am using my isp's remote server and I have emailed them for the version no. Can you request this info from the server? I am new to mysql. Thanks.

 

[TonyGroves]

You can use
SHOW VARIABLES LIKE 'version'
or
SELECT VERSION()

 

[BNPMike]

UPDATE..LEFT JOIN ... is legal for Microsoft Jet.

 

[swampBoogie]

Update a inner join b

is not standard SQL.

Mysql suports another non-standard syntax

update a,b ...

starting from version 4.0.4 so if your statement returns an error you probably have an older version of Mysql installed.

 

[UKmichael]

SELECT VERSION() returns 3.23.49 - log. Ancient. If anybody has got access to a version 4.0.4 mybe they could verify that the following does actually work, just so that we can establish a working version.


CREATE TABLE t1(
t1_id int(3) DEFAULT '0' NOT NULL auto_increment,
item_code int(3),
price float(5,2) NOT NULL,
selected int(1),
PRIMARY KEY (t1_id)
);

CREATE TABLE t2(
t2_id int(3) DEFAULT '0' NOT NULL auto_increment,
item_code int(3),
price float(5,2) NOT NULL,
PRIMARY KEY (t2_id)
);

INSERT INTO t1 (t1_id, item_code, price, selected) VALUES (0, 1,12.50,1);
INSERT INTO t1 (t1_id, item_code, price, selected) VALUES (0, 2,17.50,1);
INSERT INTO t1 (t1_id, item_code, price, selected) VALUES (0, 3,22.50,1);

INSERT INTO t2 (t2_id, item_code, price) VALUES (0, 1,12.50);
INSERT INTO t2 (t2_id, item_code, price) VALUES (0, 2,17.50);
INSERT INTO t2 (t2_id, item_code, price) VALUES (0, 3,22.50);

 

[TonyGroves]

It works in 4.0.13, and should work in any version.

By the way, you could condense your 6 INSERT statements down to 2:
INSERT INTO t1 (t1_id, item_code, price, selected)
VALUES (0, 1,12.50,1),(0, 2,17.50,1),(0, 3,22.50,1);
INSERT INTO t2 (t2_id, item_code, price)
VALUES (0, 1,12.50),(0, 2,17.50),(0, 3,22.50);

 

[UKmichael]

Tony

Thanks for the tip. Just to clarify, the following update does work on version 4.0.13?

UPDATE t2, t1
SET t2.price = t1.price
WHERE t1.item_code=t2.item_code
AND (((t1.selected)= 1))

 

[TonyGroves]

Yes, and the results look right.

 

[UKmichael]

My thanks to Tony and all other contributors.