• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

update query

Hi, I have a table1 in mysql database with two fields - user and completed. completed is an integer. I also have table2 with two fields user and completed. I want to update the completed field in table 1 with the completed field from table2 for a user.

could somebody give me the code.

thanks.
0
RupertA
Asked:
RupertA
2 Solutions
 
chapmandewCommented:
update t1
set completed = t2.completed
from table1 t1
join table2 t2 on t1.user = t2.user
0
 
BrandonGalderisiCommented:
This is an article I wrote for SQL Server.  The syntax should be the same for MySQL.

http://sqlservernation.com/blogs/howtos/archive/2009/03/19/updating-values-in-one-table-with-values-from-another-table.aspx

0
 
a_bCommented:
you need a nested query -

insert into table1 (completed) values(select completed from table2 where id = {id of required record})
0
 
RupertAAuthor Commented:
I ended up creating this which worked. your t sql didn't seem to work for me chapmandew :(

update courses
set completed=(select completed from table_bathroom_completed where courses.userref=table_bathroom_completed.userref and courses.courseID="bathrooms") WHERE EXISTS (select completed from table_bathroom_completed where courses.userref=table_bathroom_completed.userref);

However I will share the points out to chapmandew for trying and Brandon for his article.

Thanks.
0
 
BrandonGalderisiCommented:
Rupert:

The correlated sub-select that you are using should function the same as the update/from that chap and I both provided.  Care to share what "didn't work" about it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now