December 28, 2004 at 3:44 pm
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!!
December 28, 2004 at 6:09 pm
You can do this by joining in the UPDATE statement. It's best to use an alias when doing this:
UPDATE a
SET columnname = b.columnname2
FROM tablename a
JOIN tablename b ON a.key = b.key
This will act also as a filter limiting the update to the rows that match. Be sure to use the alias in place of the table name as it is a good practice to keep.
BTW: "Objects" is a terrible, terrible name for a table.
December 29, 2004 at 5:59 am
To correct your correlated subquery, you would do the following:
UPDATE Objects
SET SerialNumber =
(SELECT SerialNumber
FROM temp1
WHERE temp1.un_id = objects.Un_ID
)
This particular WHERE clause goes with the subquery so that for each objects.Un_ID, a single row is returned from temp1. Of course, you can have a WHERE clause for the UPDATE as well. For example:
UPDATE Objects
SET SerialNumber =
(SELECT SerialNumber
FROM temp1
WHERE temp1.un_id = objects.Un_ID
)
WHERE SerialNumber IS NULL
December 29, 2004 at 7:39 am
Thank you so much! That worked perfectly!
December 29, 2004 at 11:18 am
Try rewritting your query a little:
Here is my update statement:
UPDATE o
SET SerialNumber =
(SELECT t.SerialNumber
FROM temp1 t
WHERE o.Un_ID = t.un_id)
FROM Objects o
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply