Syntax error on update query 3

[mentasses]

I have the following Update query.

UPDATE ELYOSERVICES.dbo.CAREUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
SET VEHMAKE = MAKE,
VEHMODEL = MODEL,
CONVERT(Char,b.DATESOLD,103)AS LEASEND,
(CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ESLE 'Y')AS FUELCARD,
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR ='T' AND [DESCRIPTION] NOT LIKE 'Returned*'

When I try to run it I get the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

Can you help please?

 

[dbomrrsm]

Try

UPDATE ELYOSERVICES.dbo.CAREUSAGE
SET VEHMAKE = MAKE,
VEHMODEL = MODEL,
CONVERT(Char,b.DATESOLD,103)AS LEASEND,
(CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ESLE 'Y')AS FUELCARD
FROM ELYOSERVICES.dbo.CAREUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR ='T' AND [DESCRIPTION] NOT LIKE 'Returned*'

DBomrrsm

 

[mentasses]

I now get the error:
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'CONVERT'.

 

[SQLSister]

CONVERT(Char,b.DATESOLD,103)AS LEASEND needs a space between the ) and the word AS.

Questions about posting. See faq183-874

 

[mentasses]

Putting in the space I still get the same error:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'CONVERT'.

 

[SQLSister]

UPDATE ELYOSERVICES.dbo.CAREUSAGE

CONVERT(Char,b.DATESOLD,103)AS LEASEND,
(CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ESLE 'Y')AS FUELCARD
{

What field are you updating with these two statements? It doesn'tknow that's the error. Also Else is speelled wrong and the two as clauses need to go. Try this substituting the f=correct field names for what you want to update:

UPDATE ELYOSERVICES.dbo.CAREUSAGE
SET VEHMAKE = MAKE,
VEHMODEL = MODEL,
test=CONVERT(Char,b.DATESOLD,103),
test2 =(CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ElsE 'Y'end)
FROM ELYOSERVICES.dbo.CAREUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR ='T' AND [DESCRIPTION] NOT LIKE 'Returned*'

Questions about posting. See faq183-874

 

[SQLBill]

The syntax of these two lines:

CONVERT(Char,b.DATESOLD,103)AS LEASEND,
(CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ESLE 'Y')AS FUELCARD

is used for SELECT statements. Since you are doing an UPDATE you need them to be:

LEASEND = CONVERT(Char,b.DATESOLD,103),
FUELCARD = (CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ESLE 'Y')

I didn't include the SET command since that's in the rest of the code that this portion comes from.

-SQLBill

 

[mentasses]

Thanks Y'all

Works fine now