i need in one query use select, insert, delete and update.
(I need copy data from old table in to new, then delete old, and update another).
Insert and select (copy function I was able to, but now i have problem)
I have this query:
INSERT INTO news_n (id, data)
SELECT (id, data)
FROM news
WHERE id > 21
Thanks
You can't do it all in one query, but you can do it all in one transaction if you are using a transactional store engine (like InnoDB). This might be what you want, but it's hard to tell only using the information you provided in your question.
START TRANSACTION;
INSERT...;
DELETE...
UPDATE...;
COMMIT;
MySQL does not support MERGE, so you'll have to do it in two queries:
INSERT
INTO news_n (id, data)
SELECT id, data
FROM news
WHERE id > 21
ON DUPLICATE KEY UPDATE
SET data = news.data
DELETE
FROM news_n
WHERE id NOT IN
(
SELECT id
FROM news
WHERE id > 21
)
, provided you have PRIMARY KEY (id) in both tables.
In one query i dont think its possible.
You can try writing a Stored Procedure and using Triggers you may achieve that
You can't combine Select/Update/etc into one query. You will have to write separate queries for each operation you intend to complete.
SELECTwithUPDATEorDELETE.