Link to home
Start Free TrialLog in
Avatar of chrislindsay
chrislindsayFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Query issue

I am trying to update a specific field value using
UPDATE billableitem SET billableitemcategoryid=521 WHERE code='TES00000001'

The query runs with no errors and returns

(0 row(s) affected)

but doesn't update the field with the correct value.


When I use the select command to find TES00000001 vale in the table it returns the row in question

Can you help please.
Avatar of chapmandew
chapmandew
Flag of United States of America image

Its just not finding the row....this returns a value?

select * from billableitem
WHERE code='TES00000001'
Avatar of chrislindsay

ASKER

Hi chapmandew:
I can find the row and it does exist,
I need to update this row with the a different billableitemcategoryid but using the Update statement above doesn't work
Any Ideas
if this returns a row

select * from billableitem
WHERE code='TES00000001'

then this will update a value

update billableitem
set billableitemcategoryid=521
WHERE code='TES00000001'

are you sure there isn't extra criteria you're not mentioning?
if:
UPDATE billableitem SET billableitemcategoryid=521 WHERE code='TES00000001'
returns:
(0 row(s) affected)

means the WHERE clause matches 0 records.
hence, your values of 'TES00000001' is not in the table.

you might say: yes it is, but, be assured, it is not.

99% chance: the field code is of data type CHAR and not VARCHAR, for example
CHAR(20), in which case the value to match is:
'TES00000001         '
and not
'TES00000001'

please check


SELECT    *
FROM         BillableItem
WHERE     (Code = 'TES00000001')
returns
a data set of one row
When I use :
UPDATE billableitem SET billableitemcategoryid=521 WHERE code='TES00000001'
it returs: (0 row(s) affected)
I am stuck!!!
if that is "not" the problem, the only other thing I know of would be a instead of trigger on the table...
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The problem was I have two databases with identical tabels just a different database name, Thanks Guys a great help!!