0

I am trying to do something like this but I keep getting the error that "HELLO" column does not exist. The query that i am trying to run is:

delete from lib."table1" where "word"<>"HELLO" and "Language"="English"

Essentially I am trying to delete all rows where the "Language" column has English in it but no remove rows where the "word" column has "HELLO" in it. So it should skip rows where Language = English and word = HELLO, but delete all other rows with either English in Language or HELLO in word. What am i doing wrong? (if i take out the "word...ELLO" part, it works fine but deletes every English row).

Community
  • 1
  • 1
mcfly
  • 1,151
  • 4
  • 33
  • 55
  • 1
    Why is this tagged with three different RDBMS'? – Dan Aug 18 '15 at 14:57
  • 1
    `"word"` is a column name, not a string constant. Please see the manual for details: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS –  Aug 18 '15 at 15:03

2 Answers2

3

For postgres, double quotes aren't used for strings, but for column names and such

delete from lib."table1" where "word"!='HELLO' and "Language"='English'
AdrianBR
  • 2,762
  • 1
  • 15
  • 29
  • you should better use `<>` for `not equals`. – Vamsi Prabhala Aug 18 '15 at 15:01
  • Not only "for postgres". This is how it is specified in the SQL standard. And the SQL standard indeed defines `<>` for the "not equals" operator. –  Aug 18 '15 at 15:02
  • 1
    for postgres `<>` or `!=` works as not equal [Comparison Operators](http://www.postgresql.org/docs/8.2/static/functions-comparison.html) – Juan Carlos Oropeza Aug 18 '15 at 15:04
  • This works well - i was screwing up with the quotations (using python and string inserts so my quotes were off). Thanks! – mcfly Aug 18 '15 at 15:05
  • From the manual: The != operator is converted to <> in the parser stage. Fair enough, but for readability, I would not expect to see string next to <>. – AdrianBR Aug 18 '15 at 15:06
  • @a_horse_with_no_name not all sql follows the sql standard. Postgres does. Mysql doesn't. I see no issue with my formulation, while yours misleadingly suggests that standards should be expected when working with all sql. – AdrianBR Aug 18 '15 at 15:13
2

Use single-quotes for string literals and double-quotes for column names:

delete from lib."table1" where word <> 'HELLO' and Language = 'English';

On a side note: if you want to keep rows with English that have the word somewhere in the column (eg matching 'Hello World' too), then you might want to use the not like operator instead like so:

delete from table1 
where word not like concat('%','HELLO','%') and Language = 'English';
jpw
  • 44,361
  • 6
  • 66
  • 86
  • Why the `concat()`? Why not simply use `'%HELLO%'`? –  Aug 18 '15 at 16:02
  • @a_horse_with_no_name Good question. Guess I didn't think it through, but reused a pattern where the string literal would have been a string from another column, which of course isn't needed in this case. – jpw Aug 18 '15 at 16:32