novice SQL user needs help

BACKUP!!!

I think you want this:

UPDATE Item SET Supplierid = 1810 WHERE supplierid = '' AND Departmentid ='##'

The problem you have is that KI is not a DepartmentID. You need to determine the ID number of the Department named "KI" in the Department table (I think you are saying the Department NAME is "KI". This is not the same as the ID).

Do:

SELECT ID, Name FROM Department

This will get you the ID number you will substitute in the UPDATE statement.

Jason, the update statement u wrote won't affect any records, item without suppliers have Item.SupplierID=0 not ''

Chris, is 1810 the supplier code or ID? if it's the ID then u can proceed, if it's the code, plz execute this statement to know the ID of your supplier

SELECT ID FROM Supplier WHERE Code='1810'

Super Fast reply . . . Thanks a lot.

I didn't know that all of the departments had an ID, I presumed this was the two letter code that we used!

Thanks again

Chris

Nashat,

I think you are wrong. Double apostrophe will work the same as 0 in this case.

These two statements are identical and will work the same:

UPDATE Item SET Supplierid = 1810 WHERE supplierid = '' AND Departmentid ## UPDATE Item SET Supplierid = 1810 WHERE supplierid = 0 AND Departmentid = ##

Prove it to yourself... Do:

SELECT Description, SupplierID FROM Item WHERE SupplierID = ''

It will return all of the items where SupplierID is not entered (0).

Chris,

Nashat is right about the Code/ID. If 1810 is the Supplier.Code, then you need to determine the Supplier.ID and use this rather than the code to replace ## in my UPDATE statement above.

Jason

Super Fast reply . . . Thanks a lot.

I didn't know that all of the departments had an ID, I presumed this was the two letter code that we used!

Thanks again

Chris