How can I code this?

  • Hi there,

    I am trying to change the following statement so that every time the "Department" field comes up, the value "01" will be entered into it, for all my records.

    CASE

    WHEN [Department] = '' THEN

    ''

    ELSE

    REPLACE(RIGHT([Department], LEN([Department]) - CHARINDEX(': ', [Department], LEN([Department])/2)), ' ', '')

    END

    I tried using the following but I am getting an error. What am I doing wrong?

    UPDATE [Inventory item.Personnel.Transit.Department]

    SET [Inventory item.Personnel.Transit.Department.Department]=01

  • What does your data look like and what is the error message you are receiving?

  • Are you trying to update a character field with an integer value?

  • Hi Jeff,

    All the data is on a MS SQL 2008 server and is managed/modified etc. using a GUI similar to content management system. I get the following error:

    Validation failed: Error #: 0, Message: Incorrect syntax near the keyword 'UPDATE'., LineNumber: 1, Source: .Net SqlClient Data Provider, Procedure: , Number: 156; Error #: 1, Message: Incorrect syntax near the keyword 'AS'., LineNumber: 2, Source: .Net SqlClient Data Provider, Procedure: , Number: 156; Error #: 2, Message: Incorrect syntax near 'A'., LineNumber: 2, Source: .Net SqlClient Data Provider, Procedure: , Number: 102

  • I don't think its even reaching that far to validate that. I could easily put quotes before my value to not have it read as integer, I believe, right? I think right now it stops at the "Update" command.

  • sweetumz (11/17/2014)


    UPDATE [Inventory item.Personnel.Transit.Department]

    SET [Inventory item.Personnel.Transit.Department.Department]=01

    Can we see your full code and the actual error message, please? There's no WHERE clause on that UPDATe so unless you're overwriting the entire table that's not everything.

    If this IS your full code, that SET statement is ugly, and I'd still like to know the exact error, along with the schema of [Inventory item.Personnel.Transit.Department]. Do you really name your tables [soemthing.something.something.something]? If so, you end up with something like:

    SomeServeronNetwork.DatabaseName.SchemaofSomeForm.[soemthing.something.something.something].....

    EDIT: Error is above, just buried further down. Need to see the real UPDATE statement.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • sweetumz (11/17/2014)


    Hi Jeff,

    All the data is on a MS SQL 2008 server and is managed/modified etc. using a GUI similar to content management system. I get the following error:

    Validation failed: Error #: 0, Message: Incorrect syntax near the keyword 'UPDATE'., LineNumber: 1, Source: .Net SqlClient Data Provider, Procedure: , Number: 156; Error #: 1, Message: Incorrect syntax near the keyword 'AS'., LineNumber: 2, Source: .Net SqlClient Data Provider, Procedure: , Number: 156; Error #: 2, Message: Incorrect syntax near 'A'., LineNumber: 2, Source: .Net SqlClient Data Provider, Procedure: , Number: 102

    What is query tool you're using? The message is reporting syntax errors around keywords that aren't even in the statement you are proposing to run. As Craig mentioned, definitely look to put a where clause in there as you are going to update every record in that table.

  • Hi all,

    Thank you for your replies. I apologize that I haven't been as clear. I'm actually learning how to use the backend part of SQL on a CMS platform. If I don't answer your question directly, its probably because I didn't understand it so I will try to explain it the best way I know.

    To answer Craig's question, yes what I pasted was the full "Update" statement or the full code. I am using a pre-built TEM platform and when I go into the option of creating a report to extract information from the database (or to change it), it doesn't really show me all the table names etc. It just shows me all the field names and if I click on the field I want to update, the field name is populated in the SQL code box. That is how i got the "[Inventory item.Personnel.Transit.Department]". Also, because it is pre-built, I didn't name the tables. This is how the application was setup by whoever built it I guess.

    I wasn't sure if I should use SET or UPDATE for what I'm trying to achieve. I just learnt that in a basic SQL course so I was trying to see if that will fix it. What I was trying to do is put a fixed value in a column.

    Would you suggest that I take some other courses to better understand what I want to achieve? Any recommendations?

    Thanks in advance and sorry for the confusion.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply