updating a table based on condition results from another table

[samuelz]

am trying to find out what is wrong with my sql statement below, i get the following error: "The multi-part identifier "P1Totals.StudentTotals" could not be bound" what am i doing wrong?

UPDATE StudentRegistration SET Year ='2016', Term = 'Term 1' where P1Totals.StudentTotals >='300' and P1Totals.StudentTotals <='400'

[tobyITguy]

Try this.

UPDATE StudentRegistration SET Year ='2016', Term = 'Term 1' where P1Totals.StudentTotals between 300 and 400

Note: When dealing with numbers you don't need to the quotation marks.

[BitBlt]

This error usually occurs when you are referencing an identifier that has no context within your query. So, for instance, if you say

select myTable.myCol1, MissingTable.myCol2
    from myTable

it will give you the same error..."MissingTable" has no context...it doesn't appear in the "FROM" clause.

If you want to update one table based on results from querying another table, your first and best bet is to create a SELECT statement that joins the two tables and displays all the relevant data. Once you've determined that the query is correct, then you can adjust the syntax to update the table you're interested in updating.

So, for instance, from the above query you could change it to

    select myTable.myCol1, MissingTable.myCol2
        from myTable
        inner join MissingTable
        on myTable.id = MissingTable.id

Then, once you are confident you've found the correct rows you could change it to:

update myTable
set myTable.myCol = MissingTable.myCol2
    from myTable
    inner join MissingTable
    on myTable.id = MissingTable.id

Of course you'll have to change all the table names and column names to the ones that fit your scenario.

Hope this helps! Happy coding!