SQL is not recognizing table alias or datatype conversions-CodePudding

I am using Microsoft SQL Server 2019.

I assign an alias to the table 'NYPD_shootings' using the following code:

select * 
from PortfolioProjects..NYPD_shootings shootings

The above code executes without any problems, but when I reference the alias in a subsequent line of code -- for example

select OCCUR_DATE, OCCUR_TIME 
from shootings

SQL does not recognize the alias and I get the following error message:

Msg 208, Level 16, State 1, Line 11
Invalid object name 'shootings'

Also, SQL does not reflect datatype conversions. For example, I convert the 'INCIDENT_KEY' column from a float to an integer by running the following code:

update PortfolioProjects..NYPD_shootings
set INCIDENT_KEY = convert(int, INCIDENT_KEY)`

I get no error message.

I then check the datatypes by right-clicking the appropriate table and choosing the 'Design' option. The datatype for 'INCIDENT_KEY' still shows as 'float.'

I wonder if both of these are not part of the same underlying problem.

CodePudding user response：

Well, Martheen and Dale K answered, but let's formalize it.

An alias works for one query. You can't expect the (sic) subsequent line to know the previous aliases. When you ran

select OCCUR_DATE, OCCUR_TIME 
from shootings

the previous query was finished, history, gone. There is no alias shootings anymore. So SQL server tried to look for an actual table called [shootings], which does not exist.

Convert is a function that changes data. So, if your INCIDENT_KEY=3.14, then select convert(int, INCIDENT_KEY) will return 3. BUT, when you INSERT or UPDATE a table, the engine will always convert whatever your data is to the datatype of the column (yours is float). If it cannot do an implicit conversion, it will throw an error.

You cannot use insert or update to change the datatype of a column. You must use alter table:

ALTER TABLE PortfolioProjects ALTER COLUMN INCIDENT_KEY int --add not null if needed

CodePudding user response：

As comments says, alias is not working like that. But if you want you can use common table expression or temp table then you can query the CTE (common table expression). Below is an example of CTE

WITH shootings AS(
   SELECT *
   FROM  NYPD_shootings
)

select OCCUR_DATE, OCCUR_TIME 
from shootings

Secondly, update a column value will not update the underlaying table's column data type. Since float can be convertible to int the query does not throw any error. If you want to change column data type then use alter command.