CODEWITHSUNDEEP
sqlsqlitesyntax
Westley
Westley

Reputation: 1153

Column not found error with simple SQL query

I know its almost there, but base is telling me it can't find a column called table1.id when I know its there!

UPDATE table2 SET col1 = (SELECT field1 FROM table1 WHERE table2.id = table1.id) WHERE table1.id = table2.id

Upvotes: 0

Views: 1515

Answers (4)

Anil Soman
Anil Soman

Reputation: 2467

Option 1: No need to have outer WHERE clause.
Option 2: Do not use inner query unneccesarily. Use Inner Join instead

Upvotes: 0

Aaron Hathaway
Aaron Hathaway

Reputation: 4315

Instead of using a WHERE clause, try using an INNER JOIN clause. It is indeed late so forgive me for my code haha

UPDATE table2
SET col1 = (SELECT field1
            FROM table1
            WHERE table2.id = table1.id)
INNER JOIN table1
ON table2.id = table1.id

Upvotes: 0

Kashif
Kashif

Reputation: 14430

What I get from your query, this will work

UPDATE table2 SET col1 = t1.field1
FROM table2 t2 INNER JOIN table1 t1 ON t2.id = t1.id

Upvotes: 0

andrem
andrem

Reputation: 421

UPDATE table2 SET col1 = (SELECT field1 FROM table1 WHERE table2.id = table1.id)

table1 is unknown in the outer SQL.

Upvotes: 2

Related Questions