Solved

update query

Posted on 2009-05-19
5
213 Views
Last Modified: 2012-05-07
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
Comment
Question by:RupertA
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 24422472
update t1
set completed = t2.completed
from table1 t1
join table2 t2 on t1.user = t2.user
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
ID: 24422474
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
 
LVL 20

Expert Comment

by:a_b
ID: 24422479
you need a nested query -

insert into table1 (completed) values(select completed from table2 where id = {id of required record})
0
 

Author Comment

by:RupertA
ID: 24429791
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24430743
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now