how to reference another table while using UPDATE

[sid0972]

i am working on a website,on mysql and php.

 

i have two tables,

 

table 1 table 2

 

username pri_key username pri_key

field_1 field_3

field_2 field_4

 

i want to update ( if not, then insert) values in table 2, all the values, referencing table 1.

 

Like "update table 2 set username='this', field_3='....' and field_4='...' where username{from php as session variable}='name referenced from table 1';

 

I was wondering what would be the query for this.

[requinix]

MySQL 5.5 UPDATE Syntax

Multiple-table syntax:

 

UPDATE [LOW_PRIORITY] [iGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

table_references is the same thing you might give to a SELECT.

Edited January 22, 2013 by requinix

[sid0972]

yeah, but i wanted to know the query, as i do not know it myselves

kinda new to mysql.

[Barand]

As there is no reference to field1 or field2 why include table1 at all?

[sid0972]

cause these are the columns that will be users posts, updated regularly.

I have solved the problem, the 2ndt table gets updated with username automatically when an entry in 1st table is made.

 

On a side note, does anyone have a better table structure for this purpose??

[requinix]

Maybe. What are the tables for? How about some kind of description about what you're dealing with?

[sid0972]

it is a member profile system, similar to bulletin boards, forums, like this one itself (phpfreaks), with much less functionality.

 

I was asking about this do that i could store their posts in another table, and could retrieve posts according to user names and post id's.