INSERT only one Column Into One Table From another Table

Question

Hello! I have been working on this for quite a while but to no avail....

I have 2 tables:

1) dbo.[Destination - YARDI_REPORTS$]

2) dbo.[LineItemInfo]

I want to insert the values in column Case in dbo.[Destination - YARDI_REPORTS$] into

the field LIPropCaseID in dbo.[LineItemInfo].

UPDATE dbo.[LineItemInfo]
 SET        LIStateDate = cp.[Tax Service2]
 FROM     dbo.[Destination - YARDI_REPORTS$] AS cp INNER JOIN
                dbo.[LineItemInfo] AS mp ON mp.LIPropCaseID = cp.Case

I keep getting the message

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'Case'.

It is driving me crazy. The Case field is spelled correctly....please help!

Thanks!
Mike

---

Mike Kiser

Answer 1

This is probably a bad choice for a column name; chosing a column name such as "Case" that is the same as an SQL reserved word is a bad idea.  You will need to enclose the "case" column name in either double quote marks or square brackets like this:

dbo.[LineItemInfo] AS mp ON mp.LIPropCaseID = cp.[Case]

As an aside, you might see about changing the name of the column to something else; there is a good chance that this kind of error will be repeated multiple times in the future.  This is a bad design practice; talk to the table designer.

Answer 2

Try the below:

UPDATE mp
 SET        mp.LIStateDate = cp.[Tax Service2] 
 FROM     dbo.[Destination - YARDI_REPORTS$] AS cp INNER JOIN
                dbo.[LineItemInfo] AS mp ON mp.LIPropCaseID = cp.[Case]

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

always be careful in chosing the column names. Try to avoid CASE, MONTH, DATE, YEAR as column names because they are in-built SQL function and will give you such error. 

You can easily get the sense when you write such column in SSMS and if it turns PINK then they are in-built function, so avoid that. 

Now if you don't have choice then put such column names inside []

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Answer 4

Thanks Kent!

That was it! I added the brackets and everything was ok

Mike

---

Mike Kiser

Answer 5

Thanks everyone! I learned a good lesson here...

Mike

---

Mike Kiser