Sql Update statement issues...

Question

I am trying desperately to create a final table for a production envirnment, however Im having some issues....

I have 2 tables in my database. MyTable has exactly the same format as the other (AnotherTable). AnotherTable has the final value that I'd like to be in MyTable.

I am trying to update MyTable's gq value to equal the value in the AnotherTable's gq value. I can no longer use AnotherTable in production, because it is archived data...

Thus my concern. I wrote an update statement (below) that tries to do this. The issue is that I don't want to update EVERY row of the MyTable each time that this update is called. My 2nd where clause is throwing an "multipart identifier could not be bound" error. Yet I need to have the 2nd where clause there in order to assure myself that I am not updating the entire database worth of information at once.

Please help! I'm still feeling like a newb

UPDATE MyTable
SET MyTable.gq= 
    (select top 1 ANotherTable.GQ as gq 
    from AnotherTable , MyTable
    WHERE CONVERT(VARCHAR(MAX), AnotherTable.UniqueTextField)= MyTable.uniqueVarCharField
    )
WHERE CONVERT(VARCHAR(MAX), AnotherTable. UniqueTextField)
     = MyTable.uniqueVarCharField

Answer 1

Your two tables just need a join:

UPDATE m SET m.Field = o.Field
FROM MyTable m INNER JOIN otherTable o ON m.JoinedField=o.JoinedField

Answer 2

UPDATE
    M
SET
    MyTable.gq = A.gq
FROM
    MyTable M
    JOIN
    AnotherTable A ON CONVERT(VARCHAR(MAX), A.UniqueTextField)= M.uniqueVarCharField

The JOIN will restrict to where it matches.

However, you may need further filters in a WHERE clause or more JOIN conditions This will still update every row where the tables intersect