Problems with and UPDATE QUERY

[torpedon]

I try to do this query

UPDATE Articles LEFT JOIN
Modelos ON Articles.Art_Cod_Mod = Modelos.Mod_Cod SET Articles.Art_Cost_Pts = [Art_Cost_Pts]*(1+(10/100)),
Articles.Art_PVP_Pts = [Art_Cost_Pts]*(1+(10/100))
WHERE Articles.Art_Fam Like '*21' AND
Articles.Art_Prov Like '*com*' AND
Modelos.Desc LIKE '*do*';

but i can't.
I try it with Acces and it works fine, but with MySQLFront
I can't.

Please help me!!!

 

[sleipnir214]

MySQL's join syntax doesn't update across joins, only to individual tables.

______________________________________________________________________
TANSTAAFL!

 

[torpedon]

Ok, But there is another way of do this query?
Please if u know how, show me.
Thanks.

 

[sleipnir214]

Without knowing more about your database schema, I can't answer that precisely.

Perform a select which retrieves all the Mod_con values which you need to update then use that as a where clause in the update statement of Articles. ______________________________________________________________________
TANSTAAFL!

 

[torpedon]

The Scheme of Database:
Table Fields
Articles Art_Codi VARCHAR(10) Key
Art_Des VARCHAR(25)
Art_Fam VARCHAR(25)
Art_Cost_Pts DECIMAL(12,4)
Art_Cost_Eur DECIMAL(12,4)
Art_Cod_Mod VARCHAR(3) Foreign key references to Table Modelos

Modelos Mod_Cod VARCHAR(3) Key
Mod_Desc VARCHAR(25)

I want to update the table Articles filtering by the field Modelos.Mod_Desc

Have enough information?
Thanks.

 

[sleipnir214]

The second paragraph of my previous post still applies. ______________________________________________________________________
TANSTAAFL!

 

[torpedon]

How?
Can u show me the complete Query?
I must need a temporal Table of Articles for save the first SELECT?

Please, detail the steps.
Thanks.

 

[sleipnir214]

I can't tell you what is appropriate for you to do in your programming environment.


First, perform:
SELECT
Mod_cod
FROM
Modelos
WHERE
Modelos.Desc LIKE '*do*'

This will return a list of values.

Take that list of values and apply it to a single-table update. Either loop through the list of variables and perform:

UPDATE Articles
SET
Art_Cost_Pts = [Art_Cost_Pts]*(1+(10/ 100)),
Art_PVP_Pts = [Art_Cost_Pts]*(1+(10/100))
WHERE
Art_Fam Like '*21' AND
Art_Prov Like '*com*' AND
Art_Cod_Mod = <one item from list>

for each item in the list,

or transform that row of values into a comma delimited list and use the IN clause:

UPDATE Articles
SET
Art_Cost_Pts = [Art_Cost_Pts]*(1+(10/ 100)),
Art_PVP_Pts = [Art_Cost_Pts]*(1+(10/100))
WHERE
Art_Fam Like '*21' AND
Art_Prov Like '*com*' AND
Art_Cod_Mod IN (<first item>, <second item>, ,,,)
______________________________________________________________________
TANSTAAFL!

 

[torpedon]

Ok,
and thanks for all.