Translation of sql into mysql

[David Scott]

I am trying to update the column CreditCode in a table extract using data 
from another table CreditCodes, which has columns CreditCode and 
Consumer_No.


I have been given the following sql which works on another database (not 
sure which and it is late at night so I can't ring up and ask)


update extract
set CustomerCreditCode = b.CreditCode
from extract a
inner join CreditCodes b
on a.ConsumerNO = b.Consumer_No;

This gives an error in mysql:
ERROR 1064: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'from extract a

inner join CreditCodes b
on a.ConsumerNO = b.Cons

Can anyone translate it into correct mysql syntax for me?

Thanks

David Scott

_
David Scott Department of Statistics, Tamaki Campus
The University of Auckland, PB 92019
Auckland 1142,NEW ZEALAND
Phone: +64 9 373 7599 ext 86830 Fax: +64 9 373 7000
Email:  [EMAIL PROTECTED]

Graduate Officer, Department of Statistics
Director of Consulting, Department of Statistics


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

Re: Translation of sql into mysql

[Baron Schwartz]

Hi David.

David Scott wrote:


I am trying to update the column CreditCode in a table extract using 
data from another table CreditCodes, which has columns CreditCode and 
Consumer_No.


I have been given the following sql which works on another database (not 
sure which and it is late at night so I can't ring up and ask)


update extract
set CustomerCreditCode = b.CreditCode
from extract a
inner join CreditCodes b
on a.ConsumerNO = b.Consumer_No;


It looks like SQL Server or Sybase to me, but maybe other things have the same 
syntax.



This gives an error in mysql:
ERROR 1064: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'from extract a

inner join CreditCodes b
on a.ConsumerNO = b.Cons

Can anyone translate it into correct mysql syntax for me?


Here are two ways you can try, which may have different performance:

update extract set CustomerCreditCode
   = ( select CreditCode from CreditCodes as b where extract.ConsumerNO = 
b.Consumer_No);

update extract as a
   inner join CreditCodes b
 on a.ConsumerNO = b.Consumer_No
   set a.CustomerCreditCode = b.CreditCode;

Baron

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

Re: Translation of sql into mysql

[Gordan Bobic]

On Wed, 13 Jun 2007, David Scott wrote:

 
 I am trying to update the column CreditCode in a table extract using data 
 from another table CreditCodes, which has columns CreditCode and 
 Consumer_No.
 
 I have been given the following sql which works on another database (not 
 sure which and it is late at night so I can't ring up and ask)
 
 update extract
  set CustomerCreditCode = b.CreditCode
  from extract a
  inner join CreditCodes b
  on a.ConsumerNO = b.Consumer_No;
 
 This gives an error in mysql:
 ERROR 1064: You have an error in your SQL syntax; check the manual that 
 corresponds to your MySQL server version for the right syntax to use near 
 'from extract a
 inner join CreditCodes b
 on a.ConsumerNO = b.Cons
 
 Can anyone translate it into correct mysql syntax for me?

Try:

UPDATE  extract,
CreditCodes
SET extract.CustomerCreditCode = CreditCodes.CreditCode
WHERE   extract.ConsumerNO = CreditCodes.Consumer_No;

You should probably try this on a scratch database or at least take a 
backup first.

Gordan


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