sql update query - returns more than one row error

[chainedtodesk]

i have a main table and i need to verify that a field is the correct number in column1, so i tried an update query with the following code but it returns an error that "Result of SELECT more than one row." but in the combination of field2 and field3 wont have dups but i could have 10 or more records from the query that need to be updated.

UPDATE test A1
SET A1.column1= (SELECT B1.field1
FROM mycheck B1,
test C1
WHERE B1.field2 = C1.field2 and
B1.field3 = C1.field3)

i tried to add a where exists but it returned same issue. can this be done?? or can i not update multiple records like this?

UPDATE test A1
SET A1.column1= (SELECT B1.field1
FROM mycheck B1,
test C1
WHERE B1.field2 = C1.field2 and
B1.field3 = C1.field3)
WHERE EXISTS (SELECT B1.field2 , B1.field3
FROM mycheck B1,
test C1
WHERE B1.field2 = C1.field2 and
B1.field3 = C1.field3)

 

[bborissov]

UPDATE A1
SET A1.column1= B1.field1
FROM test A1
INNER JOIN mycheck B1 ON B1.field2 = C1.field2 and
                         B1.field3 = C1.field3

Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

Sorry, you should change C1 with A1 (copy & paste problem )

UPDATE A1
SET A1.column1= B1.field1
FROM test A1
INNER JOIN mycheck B1 ON B1.field2 = A1.field2 and
                         B1.field3 = A1.field3

Borislav Borissov
VFP9 SP2, SQL Server

 

[simian336]

So you ran

SELECT B1.field1
FROM mycheck B1,
test C1
WHERE B1.field2 = C1.field2 and
B1.field3 = C1.field3

and it returned more then one row? If so you could not update 1 field with it.

Work on they query till is the value you want..

Also for clarity you might consider...

SELECT B1.field1
FROM mycheck B1,
Join test C1
on B1.field2 = C1.field2
and B1.field3 = C1.field3

Simi

 

[chainedtodesk]

thanks all for the help and suggestions, will try these and post results