inserting multiple values into one row mySQL

Question

example

How can i insert multiple values into one row?

My query

insert into table_RekamMedis values ('RM001', '1999-05-01', 'D01', 'Dr Zurmaini', 'S11', 'Tropicana', 'B01', 'Sulfa', '3dd1');

i cant insert two values into one row. is there another way to do it?

' is there another way to do it?' - sql has no such ability insert into 2 rows..and explain why you want to insert into 1. — P.Salmon, Dec 01 '22 at 11:12
You cannot store in that way, store as 2 rows and format when selecting in your presentation layer. — P.Salmon, Dec 01 '22 at 11:22

score 0 · Answer 1 · answered Dec 01 '22 at 11:35

I'm ignorant of the human language you use, so this is a guess.

You have two entities in your system. One is dokter, the other is script (prescription). Your requirement is to store zero or more scripts for each dokter. That is, the relationship between your entities is one-to-many.

In a relational database management system (SQL system) you do that with two tables, one per entity. Your dokter table will contain a unique identifier for each doctor, and the doctor's descriptive attributes.

CREATE TABLE dokter(
  dokter_id  BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  nama       VARCHAR (100),
  kode       VARCHAR(10),
  /* others ... */
);

And you'll have a second table for script

CREATE TABLE script (
  script_id  BIGINT  AUTO_INCREMENT PRIMARY KEY NOT NULL,
  dokter_id  BIGINT  NOT NULL,
  kode       VARCHAR(10),
  nama       VARCHAR(100),
  dosis      VARCHAR(100),
  /* others ... */
);

Then, when a doctor writes two prescriptions, you insert one row in dokter and two rows in script. You make the relationship between script and dokter by putting the correct dokter_id into each script row.

Then you can retrieve this information with a query like this:

SELECT dokter.dokter_id, dokter.nama, dokter.kode,
       script.script_id, script.kode, script.nama, script.dosis
  FROM dokter
  LEFT JOIN script ON dokter.dokter_id = script.dokter_id

Study up on entity-relationship data design. It's worth your time to learn and will enhance your career immeasurably.

score 0 · Answer 2 · answered Dec 01 '22 at 11:46

You can't store multiple values in a single field but there are various options to achieve what you're looking for.

If you know that a given field can only have a set number of values then it might make sense to simply create multiple columns to hold these values. In your case, perhaps Nama obat only ever has 2 different values so you could break out that column into two columns: Nama obat primary and Nama obat secondary.

But if a given field could have any amount of values, then it would likely make sense to create a table to hold those values so that it looks something like:

NoRM	NamaObat
RM001	Sulfa
RM001	Anymiem
RM001	ABC
RM002	XYZ

And then you can combine that with your original table with a simple join:

SELECT * FROM table_RekamMedis JOIN table_NamaObat ON table_RekamMedis.NoRM = table_NamaObat.NoRM

The above takes care of storing the data. If you then want to query the data such that the results are presented in the way you laid out in your question, you could combine the multiple NamaObat fields into a single field using GROUP_CONCAT which could look something like:

SELECT GROUP_CONCAT(NamaObat SEPARATOR '\n')
...
GROUP BY NoRM

inserting multiple values into one row mySQL

2 Answers2