locked
UPDATE QUERY RRS feed

  • Question

  • User639567535 posted

    i have 2 tables

    table 1
    
    City_TypeID
    
    City_Type
    
    table 2
    
    ID 
    
    Name
    
    School
    
    City_TypeID


    now i create update query

    like this

    create procedure updateselect_Data
    
    @ID INT
    
    @Name nvarchar(50),
    @School nvarchar(50),
    @City_Type nvarchar(50),
    as
    Update table2
    SET 
    table2.Name =@Name ,
    table2.School =@School ,
    table2.City_Type =@City_Type 
    
    where
    
    id=@id

    now when execute updateselect_Data then how i enter city name instead of ID 

    because in form 

    there is four text box 

    Name

    School

    City

    there is text box on city not drop down

    when i execute like this THIS show error on UK 

    updateselect_Data   '11','ABC Name','DEF School','UK'

    when i execute like this 

    updateselect_Data   '11','ABC Name','DEF School','2'

    this execute successfully 

    Thursday, October 13, 2016 6:32 AM

Answers

  • User753101303 posted

    This is table1 and CityLabel is your City_Type information. I always favor explaining what should be done rather than providing ready to paste code.

    So in short my understanding is that table2.City_TypeID is a foreign key to table1. So you should find which row stores the entered City_Type, possibly creating a new row if not available already and then use this row id to update your table2.City_TypeID column (which seems what you are trying to do when looking at your table2/table1 structure).

    Another option would be to store directly this value in a table2.City_Type nvarchar column (which seems what you tried to do when looking at your current update statement though I guess using table2.City_Type=@City_Type rather than table2.City_TypeID=@City_Type is likely a typo).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 13, 2016 11:03 AM

All replies

  • User753101303 posted

    Hi,

    It seems you try to update a numeric column with a string. It seems rather that your intent would be to check if the city type label is found in the city type table to reuse its id or to insert a new row and use this new id in your main update ? Or do you want to just allow the user to select from existing "city types" ?

    Edit:

    Or do you really need a city type table? You could store the cty type label directly in the main table as it seems your intent from the update statement.

    Fine here but generally speaking it's best to never post about an error without posting the error message or the best English translation you can. It can help wrong guesses if the situation is a bit more complex than that (for example names are not matching so if not telling about the statement that works my first guess could have been a column name mismatch).

    Thursday, October 13, 2016 6:51 AM
  • User639567535 posted

    I am trying when user enter city name in textbox then this must be update  because user enter city name .. User not select city name from dropdown .. 

    Thursday, October 13, 2016 6:55 AM
  • User753101303 posted

    So it could be something such as  :

    SELECT @CityId=CityId FROM CityTable WHERE CityLabel=@CityLabel -- Find the city id if the text already exists
    -- If not found insert the new label and get its id
    IF @CityID IS NULL
    BEGIN
         INSERT INTO CityTable(CityLabel) VALUES (@CityLabel)
         SET @CityId=SCOPE_INDENTIY()
    END
    -- You can then use @CityId to update the main table
    UPDATE etc...

    Or from your update your intent is just to stote the label in the main table???

    For now the issue is that your table structure is done one way but your update statement is done as if you werre using another way (ie storing the city name directly in the main table or separately in a "reference" table).

    Thursday, October 13, 2016 7:03 AM
  • User639567535 posted

    CityTable is table 1 or table 2? and what is citylabel?

    Thursday, October 13, 2016 7:06 AM
  • User753101303 posted

    This is table1 and CityLabel is your City_Type information. I always favor explaining what should be done rather than providing ready to paste code.

    So in short my understanding is that table2.City_TypeID is a foreign key to table1. So you should find which row stores the entered City_Type, possibly creating a new row if not available already and then use this row id to update your table2.City_TypeID column (which seems what you are trying to do when looking at your table2/table1 structure).

    Another option would be to store directly this value in a table2.City_Type nvarchar column (which seems what you tried to do when looking at your current update statement though I guess using table2.City_Type=@City_Type rather than table2.City_TypeID=@City_Type is likely a typo).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 13, 2016 11:03 AM