I am trying to insert list of items from dev.test1 table, but I am struggling with logic ...
Facing following problems:
- Before insert statements I would like to add "if not exists" - select content from dev.qa_postmetas
- f.URL which I am getting in second query is not coming from correct row in dev.test1 .. I want to select f.URL where f.title = b.title
- Important information here is before executing the second insert query .. we need to get last insert postid in the first query... thats why I am using MAX(b.postid)
INSERT INTO dev.qa_posts (type, categoryid, userid, created, title, content, tags) (SELECT 'Q_QUEUED', '1', '3', NOW(), f.title, f.img, f.tagsv FROM dev.test1 f) ;INSERT INTO dev.qa_postmetas (postid, title, content) (select MAX(b.postid) , 'qa_q_extra',f.URL from dev.qa_posts b left JOIN dev.test1 as f on b.postid = f.id) ;
If not exists(select * from dev.qa_postmetas)
INSERT INTO dev.qa_posts (type, categoryid, userid, created, title, content, tags)
(SELECT 'Q_QUEUED', '1', '3', NOW(), f.title, f.img, f.tagsv
FROM dev.test1 f) ;
INSERT INTO dev.qa_postmetas (postid, title, content)
(select MAX(b.postid) , 'qa_q_extra',f.URL
from dev.qa_posts b
left JOIN dev.test1 as f on b.postid = f.id) ;
CalmSoul
ASKER
Hello Dany:
Thanks - How did we have solved the problem of f.URL which I am getting in second query is not coming from correct row in dev.test1 .. I want to select f.URL where f.title = b.title?
Ray Paseur
Maybe you can set up a simple test case showing us the data only (not the queries or logic)... Just show us the before-and-after data structures that you have and want, then we can show some ways to bridge the gap.
- I am looking for mysql script which will get data from table 1
- if TABLE 1 content column value doesn't exist in qa_postmetas
THEN
- INSERT (recursively)
and then
- GET inserted postid from qa_posts and insert values from table1 in table 3
qa_postmetas (postid <-- (this is what we have just inserted in first insert),
title, <-- this is hard coded value 'qa_q_extra'
content <-- this is coming from table 1 (URL column)
) )
Dany Balian
your post is not clear:
- GET inserted postid from qa_posts and insert values from table1 in table 3
there's no postid in table qa_posts
is postid = id from table test1?
can you complete the table structure for table1? i think it's incomplete
ErrorSQL query: Documentationinsert into qa_postmetas (postid, title, content) select q.postid, 'qa_q_extra', f.url from test1 f inner join qa_posts q on f.title=q.title and f.img=q.content and f.tagsv=q.tagswhere q.postid not in (select postid from qa_postmetas)MySQL said: Documentation#1062 - Duplicate entry '906-qa_q_extra' for key 'PRIMARY'
What are your primary keys in the last table? and where did the '906-qa_q_extra' come from? which field? because we inserted 'qa_q_extra' and not '906-qa_q_extra'.. is that the url?
insert into qa_postmetas (postid, title, content) select q.postid, 'qa_q_extra', f.url from test1 f inner join qa_posts q on f.title=q.title and f.img=q.content and f.tagsv=q.tagswhere q.postid not in (select postid from qa_postmetas) and f.url not in (select content from qa_postmetas)
same issue - #1062 - Duplicate entry '906-qa_q_extra' for key 'PRIMARY'
Dany Balian
insert into qa_postmetas (postid, title, content) select distinct q.postid, 'qa_q_extra', f.url from test1 f inner join qa_posts q on f.title=q.title and f.img=q.content and f.tagsv=q.tagswhere q.postid not in (select postid from qa_postmetas) and f.url not in (select content from qa_postmetas)
INSERT INTO dev.qa_posts (type, categoryid, userid, created, title, content, tags)
(SELECT 'Q_QUEUED', '1', '3', NOW(), f.title, f.img, f.tagsv
FROM dev.test1 f) ;
INSERT INTO dev.qa_postmetas (postid, title, content)
(select MAX(b.postid) , 'qa_q_extra',f.URL
from dev.qa_posts b
left JOIN dev.test1 as f on b.postid = f.id) ;