CODEWITHSUNDEEP
mysqlsql
Dustin
Dustin

Reputation: 63

SQL subquery error 1054 unknown column

When I run the following code I get this error message:

#1054 - Unknown column 'myvaluealerts.symbols.metadada' in 'field list'

I have read several posts similar to this one but since this is my first experience using query code for a SQL database I don't fully understand all the replies I've read. What I have read so far has helped me improve this code to where I'm only getting this error (instead of the many others I've fixed).

I have an SQL database named myvaluealerts containing three tables named symbols, users, and payments. I'm only using the symbols table with this query.

I'm trying to read a single field of data related to user 28, and paste it in the same field for user 37. The column "metadata" is comma separated text.

Update `myvaluealerts`.`symbols`.`metadata` ,  
(
   Select `myvaluealerts`.`symbols`.`metadata`
   From `myvaluealerts`.`symbols`
   Where `myvaluealerts`.`symbols`.`user_id` = 28 and 
     `myvaluealerts`.`symbols`.`symbol` = 'XOM'
) output

Set `myvaluealerts`.`symbols`.`metadata` = `output`.`metadata`
Where `myvaluealerts`.`symbols`.`user_id` = 37  and 
      `myvaluealerts`.`symbols`.`symbol` = 'XOM'

;

thanks, Dustin

Upvotes: 0

Views: 245

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Simplify your query using table aliases. You seem to have some bad table references, but I think this is what you want:

Update myvaluealerts.symbols s37 join 
       myvaluealerts.symbols s28
       on s28.user_id = 28 and s28.symbol = 'XOM'
    Set s37.metadata = s28.metadata
Where s37.user_id = 37  and 
      s37.symbol = 'XOM'

Upvotes: 1

Related Questions