June 28, 2006 at 6:49 am
I am trying to update multiple columns in one table from another table using a subquery. Getting the following error message:
Incorrect syntax near '('.
Update looks like this:
UPDATE
TBL1
SET ( COL1, COL2 ) =
( SELECT T2.COL1,T2.COL2
FROM TBL2 T2
WHERE TBL1.COL1 = T2.COL1
)
Anyone know how to accomplish this?
June 28, 2006 at 8:00 am
Walter
Not tested, but can't be far off... You just need to dispense with the SELECT. Also, I've changed your old-style join syntax to the ANSI standard.
update tbl1
set col1 = tbl2.col1, col2 = tbl2.col2
from tbl1 inner join tbl2
on tbl1.col1 = tbl2.col1
John
June 28, 2006 at 8:12 am
--as above, with minor modifications
create
table #tbl1 (col1 int, col2 int)
create
table #tbl2 (col1 int, col2 int)
insert
into #tbl1 values (1, 2)
insert
into #tbl1 values (2, 2)
insert
into #tbl1 values (2, 2)
insert
into #tbl1 values (3, 2)
insert
into #tbl1 values (4, 2)
insert
into #tbl2 values (1, 4)
insert
into #tbl2 values (2, 4)
insert
into #tbl2 values (3, 4)
insert
into #tbl2 values (5, 4)
UPDATE
#tbl1
SET
col2 = t2.col2
FROM
#tbl1 t1
INNER
JOIN
#tbl2 t2
ON
t1
.col1 = t2.col1
select
* from #tbl1
drop
table #tbl1
drop
table #tbl2
June 28, 2006 at 12:00 pm
Thanks for your quick response and help but I apoligize I left off part of the update query. Here is the complete query.
UPDATE TBL1
SET ( COL1, COL2 ) =
( SELECT T2.COL1,T2.COL2
FROM TBL2 T2
WHERE TBL1.COL1 = T2.COL1
)
WHERE
TBL1.COL1 IN (
SELECT T2.COL1
FROM TBL2 T2
WHERE TBL1.COL1 = T2.COL1)
June 28, 2006 at 1:00 pm
The query I posted above should get you this from what I can tell. If you're not getting the results you expect, why don't you post us an example of what you currently have in tbl1 and tbl2 and what you want the end result to be for tbl1?
June 29, 2006 at 8:59 am
Why are you updating COL1? Since it is your join condition, it is the same in both tables.
Is COL1 a unique value, at least in TBL2? If COL1 is not unique in TBL2 (and the duplicate rows may have different COL2 values), the final COL2 value in TBL1 is unpredictable.
Are most or all COL2 values in TBL2 different than table 1? If a large number of TBL1 rows already have the correct value, you should filter them out and only update the necessary rows.
UPDATE T1 SET COL2 = T2.COL2
FROM TBL1 T1
INNER JOIN TBL2 T2 ON T1.COL1 = T2.COL2
WHERE T1.COL2 <> T2.COL2
OR (CASE WHEN T1.COL2 IS NULL THEN 0 ELSE 1 END ^ CASE WHEN T2.COL2 IS NULL THEN 0 ELSE 1 END) = 1
If you can identify a value that would never occur in the COL2 data, such as -1 for a positive-only integer column, then the WHERE clause can be simplified to:
WHERE ISNULL(T1.COL2, -1) <> ISNULL(T2.COL2, -1)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply