[SOLVED] Updating MYSQL row using PHP

[blogfisher]

Hello,

  I am using php - mysql to update my database. I have two tables in a database. Both have same fields as below:

 

Tbl1 & Tbl2: id, name, address, phone, pin, email

I want to update row in Tbl1 where id = 3 with row in Tbl2 with id = 4.

I am using below query, however its not working...

 

UPDATE Tbl1 SET (id,name,address,phone, pin, email) WHERE id= 3 SELECT id,name,address,phone, pin, email FROM Tbl2 WHERE id = 4

 

Is this the correct way ?

[AdRock]

Why don't you select what is in table 2 id 4 and put the result into variables then update the second table using those variables

 

UPDATE tbl1 SET (id='$id', name='$name', address='$address', phone='$phone', pin='$pin', email='$email') WHERE id = 3 LIMIT 1

[blogfisher]

I can do that, however i want to know whether this is possible where i know only id and i want to update other fields automatically based on chosen id.

[Stephen68]

I found this I hope it will help you out some.

 

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafyupdatediftbl.htm

 

 

I just did a google search for it

 

Stephen

[Mikedean]

This is completely untested and I don't even think it would work, but.

 

UPDATE tb1 SET (name=(SELECT name FROM tbl2 WHERE id=4), address=(SELECT address FROM tbl2 WHERE id=4), phone=(SELECT phone FROM tbl2 WHERE id=4), pin=(SELECT pin FROM tbl2 WHERE id=4), email=(SELECT email FROM tbl2 WHERE id=4)) WHERE id = 3 LIMIT 1 

 

I'm not sure that it will work, but that's the only way I can think of it working using a query.

 

Edit:

 

Eek, or just follow the link Stephen posted

 

 

[blogfisher]

Hello Stephen ,

  I am not sure how to do it. I tried but still giving error. That example updates entire table so no need to take reference of any id. I changed it in following ways. Each one is giving the same error and not working :

 

1. UPDATE Tbl1 SET ROW = (SELECT FROM Tbl2 WHERE id = 4) WHERE id = 3

2.  UPDATE Tbl1 WHERE id = 3 SET ROW = (SELECT FROM Tbl2 WHERE id = 4)

 

Any error in the statements? or they or not logical ?

[Stephen68]

Not really sure how about

 

UPDATE tb1 SET ROW = (SELECT * FROM tb2 WHERE tb1.id = 4 AND  tb2.id=4)

 

Not sure if that would work or not, I'm not at home so I can not test it.

[blogfisher]

Stephen,

  Thats not working.... looks like this is not possible or we dont have enough knowledge to know how to do that ... ;(

[Stephen68]

Sorry man, maybe you could try something like this. Might be a long query to type out depending on how many fields you have in your tables.

 

UPDATE tb1, tb2 SET tb1.field = tb2.field, tb1.field2 = tb2.field2 WHERE tb1.id=tb2.id

 

 

Just a thought

[fesan]

Don't know if i understood this right, but cant u just use two separate mysql_queryes??

one that updates in tbl1 and one that updates tbl2....

[blogfisher]

Thanks for all your help.. finally i used simply traditional method ...updating table by simple UPDATE query by coping each field