CODEWITHSUNDEEP
sqlpostgresqlinsertnull
Beebrabie
Beebrabie

Reputation: 161

SQL How to insert null value

I want to insert data into one table from another table. In some of the columns I don't have data, so I want to set column to null. I don't know how I should do this?

This is the SQL:

INSERT INTO _21Appoint(
   PCUCODE,PID,SEQ,
   DATE_SERV,APDATE,
   APTYPE,APDIAG,D_UPDATE,CID
) SELECT (
   NULL,NULL,NULL,
   treatment_date,appointment_date,
   typeap_id,appointment_id,NULL,patient_id
) FROM cmu_treatment,cmu_appointment
  WHERE cmu_treatment.treatment_id LIKE cmu_appointment.treatment_id;

Upvotes: 1

Views: 1477

Answers (1)

user330315
user330315

Reputation:

Your insert is essentially correct. Just don't put the column list in parentheses:

INSERT INTO _21Appoint
  (PCUCODE,PID,SEQ,DATE_SERV,APDATE,APTYPE,APDIAG,D_UPDATE,CID)
SELECT NULL,NULL,NULL,treatment_date,appointment_date,typeap_id,appointment_id,NULL,patient_id
FROM cmu_treatment,cmu_appointment
WHERE cmu_treatment.treatment_id LIKE cmu_appointment.treatment_id;

In Postgres (unlike other DBMS) putting a column list in parentheses makes the result a single "record", rather then individual columns. And therefore the select only returns a single column, not multiples and thus it doesn't match the column list for the insert

another option is to simply leave out the columns completely:

INSERT INTO _21Appoint
  (DATE_SERV,APDATE,APTYPE,APDIAG,CID)
SELECT treatment_date,appointment_date,typeap_id,appointment_id,patient_id
FROM cmu_treatment,cmu_appointment
WHERE cmu_treatment.treatment_id LIKE cmu_appointment.treatment_id;

Upvotes: 2

Related Questions