Kevin Smith wrote:
>
> Hi Sasha,
>
> Many thanks for the pointer, however, I ran into more trouble. Can you
> help?
>
> I get the following errors if I try these SQL statements...
>
> REPLACE INTO BASKET (BASKET.unit_weight)
> SELECT DISTINCTROW PRODUCT_CATALOG.weight
> FROM PRODUCT_CATALOG, BASKET
> WHERE (((BASKET.product_id)=(PRODUCT_CATALOG.product_id)));
>
> ERROR 1066: Not unique table/alias: 'BASKET'
>
> REPLACE INTO BASKET (BASKET.unit_weight)
> SELECT DISTINCTROW PRODUCT_CATALOG.weight
> FROM PRODUCT_CATALOG, BASKET AS BASK
> WHERE (((BASK.product_id)=(PRODUCT_CATALOG.product_id)));
>
> ERROR 1093: INSERT TABLE 'BASKET' isn't allowed in FROM table list
>
> Thank you for your time.
>
> >Kevin Smith wrote:
> >>
> >> Hi Everyone,
> >>
> >> I'm new to the list and to MySQL, but have had a good start with it! :-)
> >>
> >> Anyhow, can anyone tell how to create an UPDATE using two tables.
> >> For example, how can I write a query similar to this..
> >>
> >> UPDATE BASKET SET item=
> >> (SELECT DISTINCTROW PRODUCTS.item_id
> >> FROM BASKET, PRODUCTS
> >> WHERE (((BASKET.id)=(PRODUCTS.id)))
> >> );
> >>
> >> Or something like that?
> >>
> >> Regards,
> >>
> >> Kevin Smith
> >> Lemon Lainey Design UK
> >> Website: http://www.lemonlaineydesign.com
> >> eMail: kevin@stripped
> >
> >Try REPLACE INTO basket SELECT ...
> >
> >--
> >Sasha Pachev
> >http://www.sashanet.com/ (home)
> >http://www.direct1.com/ (work)
> >
>
The problem has to do with the fact that you are replacing into a table
that participates in the SELECT. MySQL as far as I know cannot handle
this. See if you can re-write your select or otherwise, select into a
temporary table first.
--
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
