Updating Statement in SQL/PSQL

Question

i am new in the SQL field and i have a dumb question. Lets pretend I have a table in my database, which contains the following information:

ID        name     food
1         Max      apple
2         Anne     banana
3         Tom      kiwi

lets pretend that this is a kindergarden group that ate these foods. 2 Hors later Max ate strawberries and i want ot add this in the list as well. I want the list to look like this:

ID        name     food
1         Max      apple, strawberries
2         Anne     banana
3         Tom      kiwi

I already tried it with the UPDATE statement in SQL, but all of them want an if statement, which i personally dont understand. Does someone has an idea, how i can do it in one statement? Thanks!

Fix your data model! Don't store multiple values in a string! You need a separate table with one row per `name`/`food`. — Gordon Linoff, Mar 24 '20 at 12:17
Please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Nick, Mar 24 '20 at 12:19
lets pretend that the food are in a different tabel. apple has a food_ID of 1 and the strwaberries have food_ID 2. In the big table there would still be a 1, 2 . I want to add the second value to the table with the UPDATE statement. — menbar, Mar 24 '20 at 12:25

Hache_raw · Accepted Answer · 2020-03-24T22:30:00.553

The best thing you could do is to use several tables:

Users (ID, name)
Food (ID, name)
UserFoods (ID (optional), user_id, food_id)

Then, you could associate as many food as you want to each user. You could also add a datetime column to UserFoods if needed, etc.

*You would not use UPDATE but SET and DELETE statements...

Said that, the answer to your question would be this:

UPDATE users 
   SET food = CONCAT(food, ', ', 'strawberries') 
 WHERE name = 'Max';

Explanation: It selects the table users and it concatenates the current food, a comma and space , and strawberries to the food field of the user called Max.

I've used the user name but you should use the ID since there could be a lot of users called Max...

Updating Statement in SQL/PSQL

1 Answers1