Re: Odd Update Question.

[m i l e s]

Folks,
Thanks for the 'help'.  Oy.

I figured it out from some help on the Lasso discussion list.

All I had to do was properly address ALL the tables I wanted to touch.

So this:

UPDATE tbe_gallery
SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper
WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND
  tbe_images.img_orig_filename = tbe_gsa.gsa_id

Should have been This:

UPDATE tbe_gallery, tbe_gsa, tbe_images
SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper
WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND
  tbe_images.img_orig_filename = tbe_gsa.gsa_id

And viola, it works!

Miles.

Re: Odd Update Question.

[Rob Wultsch]

http://dev.mysql.com/doc/refman/5.0/en/update.html

See multiple table syntax.  I have had issues with the syntax (IMHO),
and is not available on 3.23 (I am a poor soul that still has to deal
3.23).

If you have all the data and you can not figure out the syntax you can
alternatively use INSERT... SELECT (and multi table works in 3.23) and
recreate the tables. I generally like this approach because it is non
destructive in that you still have the old table in case you have an
error in your logic somwhere.

On Wed, Feb 27, 2008 at 8:09 PM, m i l e s [EMAIL PROTECTED] wrote:
 Hi,

  I'm wondering if the following can be done

  UPDATE tbe_gallery
  SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper
  WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND
tbe_images.img_orig_filename = tbe_gsa.gsa_id

  Let me explain:

  I have 3 tables and only 1 of them has the correct data which I need
  to update the other two.

  The SQL statement above is based upon the following select statement
  below:

  

  SELECT tbe_gsa.gsa_id, tbe_gallery.gallery_id,
  tbe_gallery.gallery_title, tbe_gallery.gallery_price,
 tbe_gsa.gsa_sin, tbe_gsa.gsa_paperprice, tbe_gsa.gsa_canvasprice
  FROM tbe_gsa INNER JOIN tbe_images ON tbe_gsa.gsa_id =
  tbe_images.img_orig_filename
  INNER JOIN tbe_gallery ON tbe_images.img_rel_id =
  tbe_gallery.gallery_id
  ORDER BY gsa_id ASC

  

  This statement works just fine.  However the table tbe_gsa contains
  the necessary column tbe_gsa.gsa_paperprice which has a match field of
  gsa_id, which matches a field in the images table called
  tbe_images.img_orig_filename, and the images table contains a match
  field called tbe_images.img_rel_id, which matches a field in the
  gallery table called tbe_galery.gallery_id.

  So my question is how do use the corresponding match fields to update
  the necessary fields so that... be_gsa.gsa_paperprice =
  tbe_gallery.gallery_gsaprice_paper 

  Is my statement above anywhere close to what it should be 

  Miles.

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Odd Update Question.

[m i l e s]

Hi,

I'm wondering if the following can be done

UPDATE tbe_gallery
SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper
WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND
  tbe_images.img_orig_filename = tbe_gsa.gsa_id

Let me explain:

I have 3 tables and only 1 of them has the correct data which I need  
to update the other two.


The SQL statement above is based upon the following select statement  
below:




SELECT tbe_gsa.gsa_id, tbe_gallery.gallery_id,  
tbe_gallery.gallery_title, tbe_gallery.gallery_price,

tbe_gsa.gsa_sin, tbe_gsa.gsa_paperprice, tbe_gsa.gsa_canvasprice
FROM tbe_gsa INNER JOIN tbe_images ON tbe_gsa.gsa_id =  
tbe_images.img_orig_filename
	 INNER JOIN tbe_gallery ON tbe_images.img_rel_id =  
tbe_gallery.gallery_id

ORDER BY gsa_id ASC



This statement works just fine.  However the table tbe_gsa contains  
the necessary column tbe_gsa.gsa_paperprice which has a match field of  
gsa_id, which matches a field in the images table called  
tbe_images.img_orig_filename, and the images table contains a match  
field called tbe_images.img_rel_id, which matches a field in the  
gallery table called tbe_galery.gallery_id.


So my question is how do use the corresponding match fields to update  
the necessary fields so that... be_gsa.gsa_paperprice =  
tbe_gallery.gallery_gsaprice_paper 


Is my statement above anywhere close to what it should be 

Miles.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]