asked on 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) ;
MySQL ServerPHP
Last Comment
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?
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.
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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)
Did u try my scripts?
ASKER
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'
ASKER
This error is in second insert
ASKER
can we add skip if entry exist?
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?
ASKER
yes it the url which is the primary key
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)
ASKER
same issue - #1062 - Duplicate entry '906-qa_q_extra' for key 'PRIMARY'
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)
can you provide us with some sample data that you already have? and what you would like to have inserted?
ASKER
how can I share the database dump securely ? I can send you dropbox link on your email...
ASKER
send you a message ... you should have it
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) ;