setting one column equal to another column when using two tables

[darga333]

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?

[Barand]

[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]

[darga333]

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?

[Barand]

Remove single quotes round 'discount.sImagePath'

[darga333]

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?

[Barand]

What version of MySql are you using?

[darga333]

This is what the screen says:

Welcome to phpMyAdmin 2.5.6
MySQL 3.23.56

[Barand]

Now read my first post on this topic carefully

[darga333]

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?

[Barand]

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.