|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 28th, 2004, 04:20 PM
|
|||
|
|||
|
Not sure if this would be a merge or an update...
Hi, I feel like this should be fairly simple but I just can't seem to get it! I basically want to update multiple records using a sub-query.
I have two tables: objects and temp1. Both have identical column names: Un_ID and SerialNum (one has serial numbers while the other has mostly Null values for the serial numbers). I want to update the SerialNum column in "objects" with the SerialNum column from temp1. The two tables can be linked by un_id's. Here is my update statement: UPDATE Objects SET SerialNumber = (SELECT SerialNumber FROM temp1) WHERE (objects.Un_ID = temp1.un_id) The error I receive says "column prefix "temp1" does not match a table name or alias name used in the query". I think the step I am missing is actually liking the two tables in the update statement. Can anyone help? Thanks in advance!!
|
|
#2
December 29th, 2004, 04:02 AM
|
|||
|
|||
|
Code:
UPDATE Objects SET SerialNumber = (SELECT SerialNumber FROM temp1 WHERE objects.Un_ID = temp1.un_id) where un_id in (select un_id from temp1)
|
|
#3
December 30th, 2004, 12:34 PM
|
|||
|
|||
|
Wouldn't this work too Swamp?
Code:
UPDATE Objects SET SerialNumber = temp1.SerialNumber FROM temp1 WHERE objects.Un_ID = temp1.un_id AND Objects.un_id in (select un_id from temp1)
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Not sure if this would be a merge or an update... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
