-1

Table mapping:

category codes
Bread 1234, 4453
Tea 2612, 9234, 2342
Oil 9394, 4251, 8634, 1235, 6412

SQL command trying to update the codes for relevant category:

conn = sqlite3.connect('category_mapping.db')
c = conn.cursor()

query = f"UPDATE mapping SET codes = 9999 WHERE category = 'Bread'"
c.execute(query)

conn.commit()
conn.close()

But it removes all existing codes and adds 9999 to the category. I also tried:

conn = sqlite3.connect('category_mapping.db')
c = conn.cursor()

query = f"INSERT INTO mapping (category, codes) VALUES ('Bread', 9999)"
c.execute(query)

conn.commit()
conn.close()

Which adds a new category (I get two "Bread" categories). What is the SQL command to update selected category codes, without removing existing codes or adding a new category?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Isa Kurbanov
  • 81
  • 7
  • 1
    `SET codes = CONCAT(codes, ', 9999')` – Barmar Jun 18 '23 at 08:23
  • 4
    It would be better to normalize your table so you don't have a comma-delimited list in the first place. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Jun 18 '23 at 08:24
  • 1
    Never. ever store data as comma separated items. It will only cause you lots of trouble. – jarlh Jun 18 '23 at 19:14

1 Answers1

0

I found an answer:

code = "9999"
selected_category = "Bread"

query = f"UPDATE mapping SET codes = codes || ',{new_code}' WHERE category = '{selected_category}'"
Isa Kurbanov
  • 81
  • 7