1

This is probably a really obvious mistake, but I have been trying a number of things and I do not seem to be able to get the right answer.

From R, I try to execute the following DELETE SQL statement on a PostgreSQL database hosted by Digital Ocean.

sql <- "DELETE FROM tablename WHERE referenceNo IN (380,376,344,345)"
conn %>% dbExecute(sql)

I get the following error and I am unable to find the correct syntax.

Error: Failed to prepare query: ERROR:  column "referenceno" does not exist
LINE 1: DELETE FROM tablename WHERE referenceNo IN (380,37...
                                    ^
HINT:  Perhaps you meant to reference the column "tablename.referenceNo".

I have tried to follow the hint and tried SQL syntax like:

sql <- "DELETE FROM tablename WHERE tablename.referenceNo IN (380,376,344,345)"

Unfortunately, I get the same error message.

Jochem
  • 3,295
  • 4
  • 30
  • 55
  • 3
    referenceNo and referenceno are very different. referenceNo needs double quotes around it, if you really use UPPER case in your names. Use "referenceNo" to work around this issue. For the future, don't use UPPER case in your names. – Frank Heikens Jan 24 '22 at 20:44
  • https://stackoverflow.com/questions/10015531/sql-statement-error-column-does-not-exist –  Jan 24 '22 at 20:46
  • 1
    Use lowercase, and underscore if desired, for names to avoid having to use quotes as @FrankHeikens suggest. Like reference_no. – kometen Jan 24 '22 at 20:58
  • 1
    If you're struggling to implement what they're suggesting, then change your outer quotes to single-quotes, then use double-quotes around your column name. That is, `'DELETE FROM tablename WHERE "referenceNo" IN (380,376,344,345)'`. If that doesn't work, it would be beneficial (for you as well as for us) to see the true output from `select column_name from information_schema.columns where table_name like 'tablename' and column_name like 'referenceNo'` (using `like` for it case-insensitivity, I think). – r2evans Jan 24 '22 at 21:09
  • This clarified a lot! Thanks! – Jochem Jan 24 '22 at 21:40

1 Answers1

0

i already slove a problem like your's by using : "columns" and not columns, DELETE FROM tablename WHERE "referenceNo" IN (380,376,344,345)

Noureddine Zafzafi
  • 1
  • 2