2

How can i change (NULL) value of a table? I have searched everywhere but didn't find the right answer.

Works, but updates 0 rows:

UPDATE Club 
SET Place = 24
WHERE Place = NULL;

Not working:

UPDATE Club 
SET Place = 24
WHERE Place IS NULL;
Danny Beckett
  • 18,294
  • 21
  • 100
  • 129
Slavak
  • 223
  • 3
  • 11
  • On Oracle DB the "Update Club SET Place = 24 WHERE Place IS NULL" works. – Roger Lindsjö Dec 11 '11 at 16:27
  • Correlation name 'Club' not found. – Slavak Dec 11 '11 at 16:29
  • maybe isql doesn't support null try '' instead? – xQbert Dec 11 '11 at 16:40
  • I've noticed that you have asked about something similar before http://stackoverflow.com/questions/8464454/isql-sql-anywhere-trigger-gives-error. Are you sure that you are connected to proper database and the table exists? – Michał Powaga Dec 11 '11 at 16:41
  • Still the same, updates 0 rows with '' and with 0, strange. And yes, Club is table. – Slavak Dec 11 '11 at 16:43
  • @Michal Powaga, my other problem is almost solved, trigger is working, it just isn't working with the NULL. I have tested everything there, just the NULL value isn't updating. – Slavak Dec 11 '11 at 16:47
  • @Slavak this is for sybase, oracle, sql server? – aF. Dec 11 '11 at 17:09
  • @aF, "Sybase" is a company not a product. The product in this case is SqlAnywhere, not to confused with Adaptive Server Anywhere (ASE). Same company, completely different products. – Elroy Flynn Dec 11 '11 at 18:02
  • @Slavak, there's no reason this should fail to update. What do you get if you "Select * from Club where Place is null" ? – Elroy Flynn Dec 11 '11 at 18:04
  • @ElroyFlynn, It gives the right result, only the items that the Place is NULL, nothing more, it just wont update them. – Slavak Dec 11 '11 at 18:09
  • try a full qualification of the table name, specifying the owner: both for the update and the select. (e.g., "dbo.Club") – Elroy Flynn Dec 11 '11 at 18:11
  • Are there any triggers on this table? – Elroy Flynn Dec 11 '11 at 18:11
  • @ElroyFlynn, yes there are, i will delete and rewrite them, so that there will be no NULLs. Thanks all of you and sorry for the trouble. Tomorrow i will try with a new clean table and post if it was my fault that the NULLs aren't updating. – Slavak Dec 11 '11 at 18:39
  • What happens if you run `UPDATE Club SET Place = 24 WHERE Place = ''`; – a_horse_with_no_name May 24 '12 at 13:40

2 Answers2

0
UPDATE Club 
SET Place = 24
WHERE Place IS NULL;

This works fine. If its not working then, you don't have records where Place is null. Check with null case as well, as sql is case sensitive.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Swathi
  • 129
  • 3
  • 10
0

Something worth trying

Update Club
Set Place = 24
where CLUBID in (Select CLUBID from Club where place is NULL)
animuson
  • 50,765
  • 27
  • 132
  • 142
Jay Pyatt
  • 1