none
Error when updating 2 fields in a table

    Question

  • Hi

    I am wanting to update 2 fields in a table based upon 2 criteria being equal

    ie department = 001 and worktype = 01111 then then becomes department 011 and work type 02001

    DepID in the table Departments is a number  eg 1

    WorkTypeID in the table worktypes is like d3b55b03-8e1e-4150-99a4-8ff7722bfbc7

    DECLARE
    @OlddeptID INT,
    @NewdeptID INT,
    @oldworkid CHAR,
    @newworkID CHAR

    SELECT @OlddeptID = DeptID FROM Departments WHERE DeptNo = '001'
    SELECT @NewdeptID = DeptID FROM Departments WHERE DeptNo = '011'
    SELECT @OldworkID = WorkTypeID FROM WorkTypes WHERE WorkTypeCode = '01111'
    SELECT @NewworkID = WorkTypeID FROM WorkTypes WHERE WorkTypeCode = '02001'

    UPDATE MA
    SET MA.DeptID = @NewDeptID,
    SET MA.WorkTypeID = @NewWorkID
    FROM Matter MA
    WHERE MA.DeptID = @OldDeptID
    AND MA.WorkTypeID = @OldWorkID

    and I get the following message

    Msg156, Level 15, State 1, Line 14

    Incorrect syntax near the keyword 'SET'

    Any help greatly appreciated

    Graham

    Friday, February 01, 2013 4:22 PM

Answers

  • Please show us your table definition. 

    Your need to give a size for CHAR type to 36. The default value is 1.

    DECLARE 

    @OlddeptID INT,
    @NewdeptID INT,
    @oldworkid CHAR(36),
    @newworkID CHAR(36)

    ....

    • Proposed as answer by Naomi NModerator Friday, February 01, 2013 5:48 PM
    • Marked as answer by grahamdi Monday, February 04, 2013 11:08 AM
    Friday, February 01, 2013 5:46 PM

All replies

  • Hello,

    You dont use SET twice

    This should work

    DECLARE
    @OlddeptID INT,
    @NewdeptID INT,
    @oldworkid CHAR,
    @newworkID CHAR

    SELECT @OlddeptID = DeptID FROM Departments WHERE DeptNo = '001'
    SELECT @NewdeptID = DeptID FROM Departments WHERE DeptNo = '011'
    SELECT @OldworkID = WorkTypeID FROM WorkTypes WHERE WorkTypeCode = '01111'
    SELECT @NewworkID = WorkTypeID FROM WorkTypes WHERE WorkTypeCode = '02001'

    UPDATE MA
    SET MA.DeptID = @NewDeptID,
    MA.WorkTypeID = @NewWorkID
    FROM Matter MA
    WHERE MA.DeptID = @OldDeptID
    AND MA.WorkTypeID = @OldWorkID

    Friday, February 01, 2013 4:31 PM
  • You only use the keyword SET once:

    UPDATE MA
    SET MA.DeptID = @NewDeptID,
        MA.WorkTypeID = @NewWorkID
    FROM Matter MA
    WHERE MA.DeptID = @OldDeptID
    AND MA.WorkTypeID = @OldWorkID

    You maybe should take a look at the BOL entry: http://msdn.microsoft.com/en-us/library/ms177523.aspx

    Friday, February 01, 2013 4:32 PM
  • Hi

    Thanks for that

    I tried it and now get the following error message

    Msg 8170, Level 16, State 2, Line 9
    Insufficient result space to convert uniqueidentifier value to char.Graham

    Friday, February 01, 2013 4:51 PM
  • Please show us your table definition. 

    Your need to give a size for CHAR type to 36. The default value is 1.

    DECLARE 

    @OlddeptID INT,
    @NewdeptID INT,
    @oldworkid CHAR(36),
    @newworkID CHAR(36)

    ....

    • Proposed as answer by Naomi NModerator Friday, February 01, 2013 5:48 PM
    • Marked as answer by grahamdi Monday, February 04, 2013 11:08 AM
    Friday, February 01, 2013 5:46 PM