• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • 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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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