Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 19-01-2009
Member
 
Join Date: Jan 2009
Posts: 108
SQL - Update via 2 Tables

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:
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.
Reply With Quote
  #2  
Old 19-01-2009
Member
 
Join Date: Apr 2008
Posts: 193
Re: SQL - Update via 2 Tables

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
)
Reply With Quote
  #3  
Old 19-01-2009
Member
 
Join Date: Jan 2009
Posts: 108
Re: SQL - Update via 2 Tables

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

Regards,
Reply With Quote
  #4  
Old 19-01-2009
Member
 
Join Date: Mar 2008
Posts: 258
Re: SQL - Update via 2 Tables

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 ...)
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL - Update via 2 Tables"
Thread Thread Starter Forum Replies Last Post
VPN and routing tables Allison Networking & Security 4 25-11-2010 01:02 AM
How to use Tables in Java? Rob Dizzle Software Development 4 11-02-2010 07:04 AM
Subtraction between two SQL tables KADRI Software Development 3 08-12-2009 05:49 PM
How to update two database tables coldfusion Rixwel Software Development 3 10-08-2009 03:13 PM
Combining two tables !const Software Development 5 26-11-2008 07:21 PM


All times are GMT +5.5. The time now is 10:30 PM.