Insert into select and update in single query

Question

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.

Answer 1

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

INSERT INTO linksTBL (link_id,link_name,alias)
    SELECT field1, field2, field3
        FROM othertable
    WHERE inserted=0;

Answer 2

As longneck said, you cannot do multiple things in one query, but you can in a stored procedure.

Answer 3

this is not possible to do in a single query. you will have to insert the rows, then run a separate update statement.