Hello,

I am trying to perform an SQL UPDATE on my MySQL Links table. Within the Links table I have two columns one of which is a custom field and they are defined as

Contact_Email varchar(255)
URL varchar(255)

I have a 2nd table called temp with the same two columns defined in it as well.

Using an SQL UPDATE query, I am trying to move the email addresses from the 2nd table to the corresponding entries in the first Links table. Here is my query

SELECT Links.Contact_Email, Links.URL, temp.Contact_Email, temp.URL
FROM Links, temp
WHERE Links.URL = temp.URL
UPDATE Links SET Links.Contact_Email = temp.Contact_Email;

The query produces an ERROR 1064 and points to the UPDATE part of the query. Permissions seem fine as I can do simpler updates with no problem. I am running MySQL 3.23.57.

Can someone tell me what I am doing wrong? Thanks!!

You need to use an INSERT/SELECT type format, and you might want to yank the page from the MySQL.org manual, if you don't have one of the MySQL books listed on my site.

INSERT INTO Database1.Tablename1
(comma,list,of,fields)
SELECT
(comma,list,of,fields)
FROM Database2.Tablename2
ORDER BY fieldname


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Thank you for the response. I am not looking to add new records, however. I merely want to update the email field in the first table with the corresponding email that is found in the 2nd table when both URLs match.

No thanks to the book solicitation. I already own and frequently use Dubois' book on MySQL as well as MySQL.org's public documentation.

UPDATE Links SET Links.Contact_Email = temp.Contact_Email;
or
UPDATE Tablename1 SET Column1 = Tablename2.Column2
This syntax will not work because, UPDATE affects just the same table.
You would need another nested query, but in MySQL 3.23 it's not possible.

If you are using MySQL 4.x then you are ready to go :
UPDATE Tablename1 SET Column1 = ( SELECT Column2 FROM Tablename2 )

However length of column must be the same, otherwise you could have problems...

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Hi,

Need to disagree. I use Insert/Select all the time, and just posted an example. From the MySQL.com docs:



http://www.mysql.com/doc/en/INSERT.html


PS: this was the reason I did my first update to mysql, all by myself, on Solaris <G> I upgraded to 3.23 or 3.24 (don't remember exactly) just because I needed this feature.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Well, yes you are right:
http://www.mysql.com/...iff_Sub-selects.html

It says: MySQL Server until version 4.0 only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... Subselects are being implemented in the 4.1 development tree.

I did know that subselects are not supported at all before MySQL v4.0. However it seems, INSERT ... SELECT ... and REPLACE ... SELECT limited nested query syntaxes are supported in v3.23.

Thanks, Pugdog for the correction!

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

I have *really* been wanting sub selects, so, in the absence of them, I learned to do the Insert/Select stuff.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.