Correlated subquery in UPDATE - Where am I going wrong?

Question

Hi, I'm sure I'm missing something obvious, but I'm trying to get the following update to work on SQL2000, but it keeps complaining about the where clause.

UPDATE Table1
    SET Col_A = t2.Col_A, Col_B = t2.Col_B
FROM
    Table1 AS t1,
    (SELECT TOP 1 Col_A, Col_B FROM Table2 WHERE Col_C = t1.Col_C) AS t2
WHERE t1.Col_A is Null

I'm fairly certain I can get it to work with a cursor, but that's not the best way to go, is it.  Do I need to use a join?

TIA,
Menthos

Answer 1

Hi,

You can rewrite the query like this:

UPDATE TABLE1
SET COL_A = T2.COL_A, COL_B = T2.COL_B
FROM TABLE1 AS T1 INNER JOIN
(SELECT TOP 1 COL_A, COL_B, COL_C FROM TABLE2) T2 ON T2.COL_C = T1.COL_C
WHERE T1.COL_A IS NULL

What is the exact purpose of the query cause it seems a little bit strange.

Greetz,

Geert

 

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

Answer 2

Thanks Geert, I'll give that a try.

Basically it's purpose is to fill in blanks on Table1 from info on Table2... Table1 is populated from a different source to Table2 and this query basically runs through and updates Table1. 

Actually, I'm not sure your suggestion will work - the select statement using the TOP 1 clause is not going to return the top row from a selection limited by the COL_C value of Table1.

Am I making sense ??

Answer 3

Hi,

It will limit the selection based on the COL_C field. If you give it a try, you 'll see.

Greetz,

Geert

 

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

Answer 4

Hi Geert,  gave it a try and it's not working.

I know my SQL knowledge isn't stunning, but doesn't your suggestion pull the top row from the whole of Table2 and then try matching Col_C against that row?  rather than creating a rowset of rows that match Col_C then taking the TOP row of that?

Cheers,
Menthos

Answer 5

No, because the JOIN clause works the same as the WHERE clause so there is actually not much difference between them except for the time of filtering (JOIN occurs before the WHERE).

But lets focus on the intention of the query because I think there need to be a better way.

If I get it good, you want to set COL_A and COL_B to the values of table2 with the same value for COL_C and this only if COL_A is not filled in.

What do you want if you have multiple values in table2 for one value in COL_C.

Greetz,

Geert

  

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

Answer 6

OK, I'll trust you Geert :)

Yes Col_A and Col_B need to be set to the values of the same in Table2.. take a look at the following examples (apologies for quality - hopefully they'll help)

Table1

Col_A    Col_B    Col_C
null     null     001
null     null     002
Red      Green    003


Table2

Col_A    Col_B    Col_C    Col_D
Blue     Pink     002      Mars
Blue     Pink     002      Jupiter
Blue     Pink     002      Uranus
Orange   Brown    001      Saturn
Orange   Brown    001      Earth
Red      Green    003      Neptune

Cheers,
Menthos

Answer 7

Hi,

Here is the code of the setup and an example of the update statement:

/* SETUP

CREATE TABLE TABLE1
(
   COL_A VARCHAR(50),
   COL_B VARCHAR(50),
   COL_C INT
)

INSERT INTO TABLE1 VALUES (null, null, 1)
INSERT INTO TABLE1 VALUES (null, null, 2)
INSERT INTO TABLE1 VALUES ('Red', 'Green', 3)

CREATE TABLE TABLE2
(
   COL_A VARCHAR(50),
   COL_B VARCHAR(50),
   COL_C INT,
   COL_D VARCHAR(50)
)

INSERT INTO TABLE2 VALUES ('Blue', 'Pink', 2, 'Mars')
INSERT INTO TABLE2 VALUES ('Blue', 'Pink', 2, 'Jupiter')
INSERT INTO TABLE2 VALUES ('Blue', 'Pink', 2, 'Uranus')
INSERT INTO TABLE2 VALUES ('Orange', 'Brown', 1, 'Saturn')
INSERT INTO TABLE2 VALUES ('Orange', 'Brown', 1, 'Earth')
INSERT INTO TABLE2 VALUES ('Red', 'Green', 3, 'Neptune')
*/

UPDATE TABLE1
   SET TABLE1.COL_A = FILTEREDTABLE2.COL_A,
         TABLE1.COL_B = FILTEREDTABLE2.COL_B
FROM TABLE1
INNER JOIN (SELECT DISTINCT COL_A, COL_B, COL_C FROM TABLE2) FILTEREDTABLE2
ON TABLE1.COL_C = FILTEREDTABLE2.COL_C
WHERE TABLE1.COL_A IS NULL

If you run this, you will see that 2 records have been updated. A small question, what if only COL_B has nulls? It is possible to set this too like this:

UPDATE TABLE1
   SET TABLE1.COL_A = ISNULL(TABLE1.COL_A, TABLE2.COL_A),
          TABLE1.COL_B = ISNULL(TABLE1.COL_B, TABLE2.COL_B)
FROM TABLE1
INNER JOIN (SELECT DISTINCT COL_A, COL_B, COL_C FROM TABLE2) TABLE2
ON TABLE1.COL_C = TABLE2.COL_C
WHERE TABLE1.COL_A IS NULL OR TABLE1.COL_B IS NULL

With the above statement, COL_A and COL_B are only updated if they are null.

Greetz,

Geert

 

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

Answer 8

D'oh - I'd forgotten about DISTINCT - yes, that works with DISTINCT fine and dandy.

Many thanks Geert

Cheers,
Menthos