1

I want to insert into a table values which I take from another table. I am using IBExpert. Something like this:

INSERT INTO employeemove (cdemployeemove,cdclient,datestart,cdclientwhere,cdcateg)
  SELECT gen_id(EMPLOYEEMOVE_NEXTCODE, 1),
  SELECT em.name_code from employee_migration em where em.cdclient = 1,
  SELECT em.fired_date from employee_migration em where em.cdclient = 1,
  cdclientwhere, -- from employeemove
  cdcateg -- from employeemove

Is there a way to do this?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Catalina
  • 95
  • 2
  • 12
  • Are you using MySQL or Firebird? – Tim Biegeleisen Jul 24 '18 at 11:34
  • firebird, but as I know there are differences but sometimes it's the same – Catalina Jul 24 '18 at 11:35
  • 1
    Please provide sample data and desired results. – Gordon Linoff Jul 24 '18 at 11:55
  • If you really solved that many-to-many problem mentioned at https://stackoverflow.com/questions/51476351/ - then use `MERGE` command. If you did not, then there would hardly be any other way, than for you to present some significant part of input and desired output data and for us in a long Q&A session to ask you WHY you were picking one rows and not others, until we can figure out your intention. Only after this understanding would get reached "in human words" would it become possible to translate it to SQL. There's just no way to do "Something like" - everything is more or less like anything – Arioch 'The Jul 24 '18 at 12:54
  • Please do not tag with mysql if you are using Firebird, and please include DDL of the tables involved, sample data and expected result of the query. – Mark Rotteveel Jul 24 '18 at 12:55

1 Answers1

4

Presumably, you want INSERT . . . SELECT:

INSERT INTO employeemove (cdemployeemove, cdclient, datestart, cdclientwhere, cdcateg)
    SELECT gen_id(EMPLOYEEMOVE_NEXTCODE, 1), em.name_code, em.fired_date,
           cdlientwhere, cdcateg
    FROM employee_migration em 
    WHERE em.cdclient = 1;

Edit:

I'm guessing you want something like this:

INSERT INTO employeemove (cdemployeemove, cdclient, datestart, cdclientwhere, cdcateg)
    SELECT gen_id(EMPLOYEEMOVE_NEXTCODE, 1), em.name_code, em.fired_date,
           emo.cdlientwhere, emo.cdcateg
    FROM employee_migration em LEFT JOIN
         employeemove emo
         ON emo.cdclient = em.cdclient
    WHERE em.cdclient = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786