SEO

#1 (**permalink**) 02-28-2008, 06:36 AM

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: d.scott@auckland.ac.nz

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

#2 (**permalink**) 02-28-2008, 06:36 AM

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

#3 (**permalink**) 02-28-2008, 06:36 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Translation of sql into mysql