Update table based on other query 1

[mwolf00]

Sorry if this seems basic but I'm having a brain cramp this morning. I want to update a table with data from another table but I really don't need to join the two. My query from the second table returns just one row. So it looks something like this

UPDATE myTable SET
    col1 = subquery.colA
    , col2 = subquery.colB
WHERE myTable.col3 = 'some value'

[blue]subquery[/blue]
SELECT colA, colB 
FROM anotherTable 
WHERE colC = 'some other value'

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[LKBrwnDBA]

Try this:

UPDATE myTable 
   SET (col1, col2) = (
       SELECT colA, colB 
         FROM anotherTable 
        WHERE colC = 'some other value')
WHERE myTable.col3 = 'some value';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[mwolf00]

Thank you!

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook