CODEWITHSUNDEEP
phpmysqlsqljoin
PHPLover
PHPLover

Reputation: 12957

How to select and update records in one table by matching ids from it with another table?

I'm using MySQL and phpMyAdmin.

I'm a newbie to the database, RDBMS, SQL queries and all this stuff.

I've one database table called user which has following fields :

user_id(primary key) Data type : int(10) 
user_group_id Data type : smallint(4)

I've another database table called user_field which has following fields :

user_id(primary key) Data type : int(10) 
country_child_id Data type : mediumint(8)

Now I want select query and update query for above tables.

In select query it should return me the results from table user_field where user_group_id = 6 and user.user_id = user_field.user_id.

In update query I want to update the country_child field(country_child = 1398) from table user_field where user_group_id = 6 and user.user_id = user_field.user_id.

Can some one please help me in building my queries?

Thanks.

Upvotes: 2

Views: 121

Answers (2)

Vince Tino
Vince Tino

Reputation: 142

Try to get the records you want to update by using INNER JOIN. You can try this query:

UPDATE a
SET a.country_child_id = 1398
FROM user_field AS a
INNER JOIN user AS b ON a.user_id = b.user_id
WHERE b.user_group_id = 6

Hope it helps.

EDIT:

FOR MySQL 

UPDATE user_field
INNER JOIN user ON user.user_id = user_field.user_id
SET user_field.country_child_id = 1398
WHERE user.user_group_id = 6

I'm sorry the first update statement will only work in MSSQL. @Kendal is right, in MySQL SET clause comes after the INNER JOIN.

Upvotes: 1

Kendall
Kendall

Reputation: 381

I recommend a where exists clause for this type of update, like this: http://sqlfiddle.com/#!9/0852a/1

update user_field
set country_child_id = 1398
where exists (
  select * from user
  where user.user_id = user_field.user_id
  and user.user_group_id = 6
)

In the sample fiddle, you'll notice that two records are updated -- user_id = 2 and user_id = 6 are both part of user_group_id = 6.

EDIT:

I should mention that I like that syntax because I find it easy to read, though I should probably also mention that you can use a more explicit inner join. Just keep in mind that the syntax for mySQL seems to be slightly different than others, in that the join clause comes before the set clause.

At least, that's how I got it to work: http://sqlfiddle.com/#!9/de73e/1

update user_field
inner join user
on user.user_id = user_field.user_id
set country_child_id = 1398
where user.user_group_id = 6

Try them both and let me know which one's faster. Cheers!

Upvotes: 0

Related Questions