Here is my dayly headbreaking problem to you all, After hours of trieing and reading the manual regarding REPLACE INTO command and INSERT INTO, looking for a way to be able to achieve the following, was not very sucessfull ( can MySQL do what I want in this case ? ) (running MySQL ver 3.23.41 on RedHat Linux 7.2)
here is the case : I have several different tables, in my case there are 3 tables I need to work with. I have made 3 tables and want to update/replace some fields/or a column in "mytable" which is a price list, with some new produkt prices from the other two tables, but ONLY the "price" column in 'mytable' needs to be changed/replaced and the rest of the row/rows in 'mytable' must remain as is, affter all I just want to update the new prices. mytable: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | artikelnr | varchar(30) | | PRI | | | | code | varchar(30) | YES | | NULL | | | prod_group | varchar(60) | YES | | NULL | | | category | varchar(30) | YES | | NULL | | | comment | text | YES | | NULL | | | price | float(6,2) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) Othertable1: +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | artikelnr | varchar(30) | | PRI | | | | comment | text | YES | | NULL | | | groepnaam | varchar(60) | YES | | NULL | | | price | float(6,2) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) Othertable2: +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | productgroep | varchar(50) | YES | | NULL | | | artikelnr | varchar(10) | | PRI | | | | comment | text | YES | | NULL | | | price | float | | | 0 | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) Because it is ompossible to write data into a table you are selecting from, I made a copy/mirror of 'mytable' and called it 'mytable_image' , so I use this image table to do SELECT from it, so I can replace/update into "mytable" without problems , you'l see what I mean in the query below. Also It is not possible with REPLACE INTO to tell it to update just one column with data selected out of columns from multiple tables, therefore I needed to issue the ' REPLACE INTO mytable(price)'... query as many times as I have other tables :-( here are the query's I used to do this: REPLACE INTO mytable(price) SELECT othertable1.price FROM mytable_image, othertable1 WHERE othertable1.artikelnr=mytable_image.artikelnr ---------------------- REPLACE INTO mytable(price) SELECT othertable2.price FROM mytable_image, othertable2 WHERE othertable2.artikelnr=mytable_image.artikelnr ------------------- Unfortunatly this replaces the whole row, inserts the new prices in the correct column and leaves the rest of the fields in the table 'blank' !! with other words it fills them with the "default" value . Is there a way in MySQL to replace fields in one column with data taken from other tables and leave the rest of the table as is ? I know I can do that manually updating only the fields I want with hand by means of Value() statements, but It must be updated from data allready in other tables. I'm just a newbie which now knows something about MySQL after lot of evenings reading, reading, reading and... reading a lot documents and use the good book from Paul DuBois, and finding out that there is more to it than what you see on the surface once you do realworld tasks with it :-) Thanks, -- Fouad --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
