Creating a query to insert data into MySQL using foreign keys

2
votes

I am a total newbie at DB's and I'm trying to insert data into the following tables:

Table Start

Table = users

Columns = id , username , password

Table = users_info

Columns = id , e-mail , address , logged , portrait , user_id

Table End

users_info.user_id is a foreign key linked to users.id.

I want to build a query that will insert data into (users_info table) based on information from (users table)... literal i.e.:

Insert portrait into users_info where user_id = users.id and username = JohnDoe

What is the syntax to get this going?

Thanks!!

2
mysqlsqlsql-insert
No I want to insert data into users_info on the row that matches the user_id = users.id. This is not for creating a new entry or row, but to update an existing one. Yes the id column on both tables are primary keys on AUTO_INCREMENT - DeviArt

2 Answers

1
votes

If what you want to do is update the data in users_info table then you have to use the UPDATE command.

UPDATE users_info a SET a.portrait = 'value1', a.logged = 'value2', a.address = 'value3', a.email = 'value4' where a.user_id = (SELECT DISTINCT(id) FROM users b WHERE b.username = 'JohnDoe')

Basics on SQL commands on http://www.w3schools.com/sql/

Good Luck!

0
votes

Try this:

Insert portrait into users_info 
(id, email) VALUES ( select user.id, '[email protected]' )
where user_id = users.id and username = 'JohnDoe'
)