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

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

Answer 2

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

Answer 3

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 4

To get the workaround and Step by Step description for developing SSIS package in order to overcome the issue with SSIS while importing text files with Flat File Connection Manager and Flat File Source where the "Row Delimiter" property does not work properly for rows having NULL or empty values, follow the below link:

http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/ Thanks,