Jump to content


Photo

setting one column equal to another column when using two tables


  • Please log in to reply
9 replies to this topic

#1 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 28 May 2006 - 06:46 AM

Hi I am trying to set t1.sImagePath=t2.sImagePath and it is not working for some reason

This code selects what I need
SELECT t1.sImagePath,t2.sImagePath FROM members t1, discount t2 WHERE t1.bBusiness = 1 AND t1.user_id = t2.iMemberId AND t2.sActive = 1 GROUP BY t1.user_id

does anyone know how to do that?

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 28 May 2006 - 10:14 AM

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Note: you cannot use ORDER BY or LIMIT with multiple-table UPDATE.


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 28 May 2006 - 06:50 PM

Hey for some reason it is giving me the following error:

#1064 - You have an error in your SQL syntax near '
discount SET members.sImagePath = 'discount.sImagePath' WHERE memb' at line 1

UPDATE members,discount SET members.sImagePath='discount.sImagePath' WHERE members.user_id = discount.iMemberId

does anyone know what i am doing wrong?



#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 28 May 2006 - 06:52 PM

Remove single quotes round 'discount.sImagePath'
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 28 May 2006 - 07:33 PM

it still gives me the same error

I dont understand what I could be doing wrong

I have it set up exactly how you are saying

UPDATE members,discount SET members.sImagePath=discount.sImagePath WHERE members.user_id = discount.iMemberId

i am trying to set members.sImagePath = discount.sImagePath ... could it possibly be because I set members.sImagePath as null when i created the column?



#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 29 May 2006 - 12:12 AM

What version of MySql are you using?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 29 May 2006 - 02:34 AM

This is what the screen says:

Welcome to phpMyAdmin 2.5.6
MySQL 3.23.56


#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 29 May 2006 - 09:24 AM

Now read my first post on this topic carefully
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 darga333

darga333
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 01 June 2006 - 04:13 AM

Hey Barand, do you know of a way with the older version of mysql? I am going to upgrade mysql but at a later date.

I did several searches online and tried to come up with a solution before replying back to this topic. I have another question also. It said that i was tryign to do was a multiple table update when infact I am just updating 1 table. Maybe you might know why that is?

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 01 June 2006 - 06:13 AM

UPDATE members,discount

Two table name therefore multiple.

You would have to do it with PHP - loop through the discount records and update each matching member record.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users