SQL - Update via 2 Tables

[Bantu]

Hello,

I will post the message to find a solution to my problem

I must change the value of several items from one of my tables in PhpMyAdmin me using a column to another table as a criterion for change


my tables

Code: 
-- 
- Structure of the table `series` 
-- 
CREATE TABLE `series` ( 
`id_serie` int (10) unsigned NOT NULL auto_increment, 
`nom_serie` varchar (255) NOT NULL default'', 
`type` enum ( 'type1', 'type2', 'type3') NOT NULL default 'type1', 
`type` varchar (70) NOT NULL default'' 
`themes` varchar (255) NOT NULL default'', 
`adult` enum ( 'YES', 'NO') NOT NULL default 'NO' 
`state` enum ( 'ongoing', 'finished', 'unfinished to this day', 'stopped','') NOT NULL default' ongoing ', 
`Country` varchar (50) NOT NULL default'', 
`nbre_total` int (10) unsigned NOT NULL default'0 ', 
`nbre_parus` int (10) unsigned NOT NULL default'0 ', 
`author` varchar (255) NOT NULL default'', 
`designer` varchar (255) NOT NULL default'', 
`resume` LONGTEXT NOT NULL, 
`` texte_libre LONGTEXT NOT NULL, 
`editor` varchar (255) NOT NULL default'', 
PRIMARY KEY ( `id_serie"), 
KEY `adult` ( `` adult) 
KEY `author` ( `author`) 
KEY `designer` ( `` designer) 
KEY `editor` ( `` editeur) 
KEY `state` ( `` etat) 
KEY `type` ( `type`) 
KEY `id_serie` ( `` id_serie) 
KEY `nbre_parus` ( `` nbre_parus) 
KEY `nbre_total` ( `` nbre_total) 
KEY `nom_serie` ( `` nom_serie) 
KEY `country` ( `country`) 
KEY `themes` ( `` themes) 
KEY `type` ( `type`) 
) TYPE = MyISAM AUTO_INCREMENT = 2191; 
-- 
- Structure of the table `articles` 
-- 
CREATE TABLE `articles` ( 
`name` varchar (255) NOT NULL default'', 
`series` int (10) unsigned NOT NULL default'0 ', 
`package` enum ( 'YES', 'NO') NOT NULL default 'NO' 
`price` float NOT NULL default'0 ', 
`date` date_sortie NOT NULL default'0000-00-00 ' 
`` prix_editeur float unsigned NOT NULL default'0 ', 
PRIMARY KEY ( `name`, `Series"), 
KEY `package` ( `box"), 
KEY `date_sortie` ( `` date_sortie) 
KEY `name` ( `name`) 
KEY `price` ( `price`) 
KEY `prix_editeur` ( `` prix_editeur) 
KEY `Series` ( `` Series) 
) TYPE = MyISAM;

My problem:
Replace the contents of the "price" with "nouveau_prix" and the contents of the field "prix_editeur" with "nouveau_prix_editeur" for all records of which the "price" is equal to "telle_valeur" and the "editor" is equal to "tel_editeur.

A:
Problem:
Replace the contents of the "5.65" with "5.95" and content of the "5.75" with "6.10" for all records of which the "price" is equal to "5.65" and the "editor" is equal to " editeur1.


I must change the price prix_editeur "" price "with new value and for different value.
Basically I have to do so for several publishers who change their price ranges for each [eg: 5.65 by 5.95, 5.50 by 5.75, 6.92 by 7.25 (without the amendment contained in the "prix_editeur")] to make the link between I have 2 tables "Series" and "id_serie"


Thank you for all the possible answers, I hope I am sufficiently clear if any doubt please tell me.

[Mecurtis]

The answer to your query result result will be as follows:

SQL query: Documentation

UPDATE Articles SET price ='5 .95 ',
prix_editeur ='6 .10 'WHERE IN Series (
SELECT s.id_serie
FROM articles a, s series
WHERE a.prix ='5 .65 '
AND s.editeur = 'editeur1'
AND s.id_serie = a.serie
)

[Bantu]

Thanks for your reply,You have point out the proper thing out of this problem thanks for noticing me that.

Regards,

[Khushal]

A priori (to take with tweezers, I'm not a pro MySQL), I would say that MySQL rattle because you trying to do an update on a table that you use in your query selection. Basically, it creates a loop ...

You should test your request already just SELECT, see history.

And since we are in the books, you may need to create a table with price ranges.
In fact, your tables do not look very Relational.

I tend to do (assuming a series remains in a single editor ...) an extra table:
RATES ((ID_Editeur, ID_Tarif) PK, Price, Prix_Editeur)

By the way, creates tables for your types and states.
And review some of the tables (a publisher can change along the way ...)