Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi,
I have to update one table in sql
i am using the following query:
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
Zukiari1.4K
Edited 13-Jun-12 2:45am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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:
 
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).
  Permalink  
Comments
Zukiari at 14-Jun-12 1:11am
   
Actually, the makeready table contains only one row wich is having the same date,shift and ns values. But in rdc_lb table there are multiple rows with same date,shift and ns values. I want to update the mrop value in each row of rdc_lb table with the value from makeready table.
 
Thank you.
Tim Corey at 14-Jun-12 7:50am
   
Then this query will work. If it does not, then your issue is that the data does not exactly match. My guess is that the makeready date field has just a date (which then implies a midnight value for the time) whereas your rdc_lb table has actual date/time values (which will cause a mismatch).
Zukiari at 15-Jun-12 0:20am
   
Thanks a lot.... There was some mismatch on the data.
 
Thank you
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Dear Friend,

Hope this sample query will be helping you.

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

 
Regards,
AP
  Permalink  
v2

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 231
1 OriginalGriff 223
2 nv3 50
3 VJ Reddy 45
4 Mehdi Gholam 45
0 Sergey Alexandrovich Kryukov 6,696
1 OriginalGriff 6,149
2 CPallini 2,473
3 Richard MacCutchan 1,697
4 Abhinav S 1,560


Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 14 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100