Jump to content

Archived

This topic is now archived and is closed to further replies.

darga333

setting one column equal to another column when using two tables

Recommended Posts

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

This code selects what I need
[code]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[/code]

does anyone know how to do that?

Share this post


Link to post
Share on other sites
[quote]
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.

[/quote]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.