update one table using values from another table in sql

Question

1.00/5 (1 vote)

See more:

Hi,
I have to update one table in sql
i am using the following query:

SQL

UPDATE rdc_lb
 set
 rdc_lb.mrop=(select makeready.mrop from makeready where makeready.date=rdc_lb.date and makeready.shift=rdc_lb.shift and makeready.ns=rdc_lb.ns)

but it is not working fine.

How can I update the table?

Thank You

Posted 13-Jun-12 2:43am

Zukiari

Updated 13-Jun-12 2:45am

Prasad_Kulkarni

v2

Add a Solution

2 solutions

Solution 3

Dear Friend,

Hope this sample query will be helping you.

SQL

update BLL set status='CLOSE' from
--select * from
BLL a (nolock), BML b (nolock) where a.ID=b.ID

Regards,
AP

Posted 13-Jun-12 17:59pm

Arunprasath Natarajan

Updated 14-Jun-12 7:11am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Accepted Answer · 2012-06-13T03:03:00

Your syntax is correct (as long as the tables actually have the fields you specified). I replicated the query you have here on my own system and it ran as expected. My assumption, then, is that the issue is with the data. Most likely your data doesn't quite match or you have a duplicate. Let me explain each possibility.

One way your data might not match is if the date fields are storing actual dates that weren't copied from one to the other. Date fields usually store the entire date/time value, which means that two values might be on the same day but won't be equal because they are one second different in time (or less depending on the precision of your date type). You should check to see how the dates are stored. It would be an expensive query, but you might have to parse out just the date portion of each value and compare them (or create a calculated field).

Duplicate rows that match your criteria will also be a problem. If you have two rows that match your criteria then the sub-query will return two or more rows. That isn't ok. SQL will crash. To fix this, you could limit your sub-query to the TOP 1 rows like so:

SQL

UPDATE rdc_lb
SET rdc_lb.mrop=(SELECT TOP 1 makeready.mrop FROM makeready WHERE makeready.date=rdc_lb.date and makeready.shift=rdc_lb.shift AND makeready.ns=rdc_lb.ns)

That only works if it is ok to choose only the first row (if the second row would have the same data, for instance). Otherwise, you are going to need to find a different way to figure out which row to choose (maybe order them in descending order based upon creation date and then select the top one so you get the latest value).