Jump to content


Photo

how to reference another table while using UPDATE


  • Please log in to reply
6 replies to this topic

#1 sid0972

sid0972

    Advanced Member

  • Members
  • PipPipPip
  • 50 posts

Posted 22 January 2013 - 04:17 PM

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.

#2 requinix

requinix

    Transforming Moderator

  • Moderators
  • 6,307 posts
  • LocationWA

Posted 22 January 2013 - 04:29 PM

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 by requinix, 22 January 2013 - 04:30 PM.


#3 sid0972

sid0972

    Advanced Member

  • Members
  • PipPipPip
  • 50 posts

Posted 22 January 2013 - 05:00 PM

yeah, but i wanted to know the query, as i do not know it myselves
kinda new to mysql.

#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,769 posts
  • LocationCheshire, UK

Posted 22 January 2013 - 05:04 PM

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 sid0972

sid0972

    Advanced Member

  • Members
  • PipPipPip
  • 50 posts

Posted 23 January 2013 - 03:54 AM

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??

#6 requinix

requinix

    Transforming Moderator

  • Moderators
  • 6,307 posts
  • LocationWA

Posted 23 January 2013 - 02:19 PM

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

#7 sid0972

sid0972

    Advanced Member

  • Members
  • PipPipPip
  • 50 posts

Posted 24 January 2013 - 02:29 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com