Insert into select and update in single query
- by Ossi
I have 4 tables: tempTBL, linksTBL and categoryTBL, extra
on my tempTBL I have: ID, name, url, cat, isinserted columns
on my linksTBL I have: ID, name, alias columns
on my categoryTBL I have: cl_id, link_id,cat_id
on my extraTBL I have: id, link_id, value
How do I do a single query to select from tempTBL all items where isinsrted = 0 then insert them to linksTBL and for each record inserted, pickup ID (which is primary) and then insert that ID to categoryTBL with cat_id = 88. after that insert extraTBL ID for link_id and url for value.
I know this is so confusing, put I'll post this anyhow...
This is what I have so far:
INSERT IGNORE INTO linksTBL (link_id,link_name,alias)
VALUES(NULL,'tex2','hello'); # generate ID by inserting NULL
INSERT INTO categoryTBL (link_id,cat_id)
VALUES(LAST_INSERT_ID(),'88'); # use ID in second table
I would like to add here somewhere that it only selects items where isinserted = 0 and iserts those records, and onse inserted, will change isinserted to 1, so when next time it runs, it will not add them again.
