mysql query issue
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)
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) ;
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) ;
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?
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?
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 have 3 tables
TABLE 1 COLUMNS:
TABLE 2 COLUMNS:
TABLE 3 COLUMNS:
- 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)
- 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)
) )
TABLE 1 COLUMNS:
`test1`(`id`, `title`, `URL`, `img`, `tagsv`
TABLE 2 COLUMNS:
qa_posts (type, categoryid, userid, created, title, content, tags)
TABLE 3 COLUMNS:
qa_postmetas (postid, title, content)
- 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)
INSERT INTO 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)
- 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)
) )
your post is not clear:
is postid = id from table test1?
can you complete the table structure for table1? i think it's incomplete
- GET inserted postid from qa_posts and insert values from table1 in table 3there'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
try this!
however it would be better if you have a foreign key between test1 and qa_posts
//this sql will insert everything missing into qa_posts
INSERT INTO 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 where f.url not in (select content from qa_postmetas)
//next insert everything inserted here into qa_postmetas (i assume there's an autoincrement field called postid in this table)
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.tags
where q.postid not in (select postid from qa_postmetas)
however it would be better if you have a foreign key between test1 and qa_posts
Hello Dany:
Thanks ...
Yes you are correct table 2 has
qa_posts (postid, type, categoryid, userid, created, title, content, tags)
Thanks ...
Yes you are correct table 2 has
qa_posts (postid, type, categoryid, userid, created, title, content, tags)
I am getting error
Error
SQL query: Documentation
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.tags
where 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?
try this:
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.tags
where q.postid not in (select postid from qa_postmetas) and f.url not in (select content from qa_postmetas)
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.tags
where q.postid not in (select postid from qa_postmetas) and f.url not in (select content from qa_postmetas)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial