(SOLVED) Re: Error 1064: update .....select nested.

[Truong Tan Son]

Dear Sir,

All solutions is very good !
update TABLE1 a
INNER JOIN TABLE2 b
       on a.id = b.other_id
set a.FIELD1=b.FIELD2
WHERE a.column1='literal';

Or

update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where column2='[EMAIL PROTECTED]'
And column1 = '[EMAIL PROTECTED]'

Or

update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where b.column2=a.column1
And column1 = '[EMAIL PROTECTED]'


Thanks you very much.


----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: "Ing. Edwin Cruz" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>; "'Truong Tan Son'" <[EMAIL PROTECTED]>
Sent: Thursday, March 16, 2006 10:51 PM
Subject: RE: Error 1064: update .....select nested.


AAAUUUGGGHHH!!! You used the dreaded "comma separated list" !!!  ;-)

A more explicit way to write the same thing posted by Sr. Cruz...

update TABLE1 a
INNER JOIN TABLE2 b
       on a.id = b.other_id
set a.FIELD1=b.FIELD2
WHERE a.column1='literal';

Actually posting a real query (instead of a cleansed "column1"..."column2"
query) would have resulted in you getting better answers.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Ing. Edwin Cruz" <[EMAIL PROTECTED]> wrote on 03/16/2006 10:24:05 AM:

update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where column2='[EMAIL PROTECTED]'
And column1 = '[EMAIL PROTECTED]'



Or:


update TABLE1 a, TABLE2 b set a.FIELD1=b.FIELD2
where b.column2=a.column1
And column1 = '[EMAIL PROTECTED]'


Regards!

-----Mensaje original-----
De: Truong Tan Son [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 15 de Marzo de 2006 07:22 p.m.
Para: mysql@lists.mysql.com
Asunto: Error 1064: update .....select nested.


Dear Sir,

MySQL-4.1.12 on RedHat Linux EL4:

mysql> update TABLE1 set FIELD1=(select FIELD2  from TABLE2 where
mysql> COLUMN2=
'[EMAIL PROTECTED]') where COLUMN1='[EMAIL PROTECTED]';

ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresp onds to your MySQL server version for the right syntax to use
near
'select FIELD2 from TABLE2 where COLUMN2='[EMAIL PROTECTED]

mysql>

For MySQL -5.0.18   is   Ok

Syntax error for version 4.1.12 ?

Thanks you and best regards,
--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]