Member Avatar for katiiiii

pleaseeeeeeee hepl meeeeeeee!!!!!!!!!

i have 2 tables :
time1 (noo integer,days varchar,times varchar,Tindex integer,primary key
(noo,Tindex))

timetable1_1(COn integer,coursename varchar,dayss varchar,timess varchar,
primary key(COn))


i have created a new table temptime1 and tried to fill it with the rows in time1 which their days and times are not available in timetable1_1

i used the below query but it gives me error: "Operand should contain 1 column"


QUERY:

insert into TempTime1 (NOM,Tday,Ttime,Ttindex) select * from time1 where days and times not in (select * from timetable1_1)
  • 3 Contributors
  • 3 Replies
  • 105 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by rronyy

Dani AI

Generated

Brief summary: the goal is to insert rows from time1 into TempTime1 only when the same (day, time) pair does not exist in timetable1_1. The original error came from two separate problems: using SELECT * into a different-numbered column list (as pointed out), and an incorrect multi-column test like days and times NOT IN (...) (seen in the original post and ’s follow-up). The UNION approach used by returns rows where the day OR the time is missing in the other table, which is not the same as “pair not present.”

Recommended, safe patterns

Use NOT EXISTS (robust with NULLs) and always list target columns explicitly:

INSERT INTO TempTime1 (NOM, Tday, Ttime, Ttindex)
SELECT t.noo, t.days, t.times, t.Tindex
FROM time1 t
WHERE NOT EXISTS (
  SELECT 1 FROM timetable1_1 s
  WHERE s.dayss = t.days AND s.timess = t.times
);

An alternative is LEFT JOIN with an IS NULL test on the joined table’s PK:

INSERT INTO TempTime1 (NOM, Tday, Ttime, Ttindex)
SELECT t.noo, t.days, t.times, t.Tindex
FROM time1 t
LEFT JOIN timetable1_1 s
  ON s.dayss = t.days AND s.timess = t.times
WHERE s.COn IS NULL;

Practical notes and troubleshooting

  • Always run the SELECT part first (without INSERT) to verify results.
  • Ensure the INSERT column list matches the SELECT column count and order; avoid SELECT * unless tables are identical.
  • NOT IN with tuples is supported in some engines but can break if inner rows contain NULL; prefer NOT EXISTS or LEFT JOIN.
  • Add an index on (dayss, timess) in timetable1_1 for performance.
  • If TempTime1 has a PK that may conflict, consider INSERT IGNORE or handle duplicates explicitly in a transaction.

This ties together ’s column-count warning and the join idea, clarifies why ’s UNION behaved differently, and gives simple, reliable queries to use.

Recommended Answers

All 3 Replies

Member Avatar for varmadba

INSERT INTO TempTime1 (NOM,Tday,Ttime,Ttindex) SELECT * FROM time1 WHERE days AND times NOT IN (SELECT * FROM timetable1_1) Few things to note here

you are inserting values into 4 columns of TempTime1 table with
select * from time1
where time1 has more than 4 columns,so that's a error

and the second one : SELECT * FROM time1 WHERE days AND times NOT IN (SELECT * FROM timetable1_1) you can't put 2 columns "days AND times" and say not in.......
and then you are trying to GET times which are not in timetable1_1
you have specify
select times from timetable1_1 as the inner query is returning more than 1 column


try this

INSERT INTO TEMPTIME1
SELECT TOTAL.DAYS,TOTAL.TIMES,TOTAL.TINDEX FROM
(SELECT TIME1.DAYS,TIME1.TIMES,TIME1.TINDEX FROM
 TIME1
 LEFT OUTER JOIN TIMETABLE1_1
      ON TIME1.TIMES=TIMETABLE1_1.TIMES
       AND TIME1.DAYS=TIMETABLE1_1.DAYS
 HAVING (TIMETABLE1_1.TIMES IS NULL AND TIMETABLE1_1.DAYS IS NULL)
)TOTAL
Member Avatar for katiiiii

yeah you are right . i was in a big mistake.
so i used the below query and it worked:

insert into TempTime1(Tday,Ttime) select days,times from time1  where days not in (select dayss1 from timetable1_1 ) union select days,times from time1  where times not in (select timess1 from timetable1_1 )
Member Avatar for rronyy

It's really simple...
all you have to do is replace inner * with your desired columns

The correct QUERY would be:

insert into TempTime1 (NOM,Tday,Ttime,Ttindex) select * from time1 where days and times not in (select days, times from timetable1_1)

QUERY:

insert into TempTime1 (NOM,Tday,Ttime,Ttindex) select * from time1 where days and times not in (select * from timetable1_1)
Edited by mike_2000_17 because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.