mysql query issue

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) ;

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?

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:

`test1`(`id`, `title`, `URL`, `img`, `tagsv`

Select all Open in new window

TABLE 2 COLUMNS:

qa_posts (type, categoryid, userid, created, title, content, tags)

Select all Open in new window

TABLE 3 COLUMNS:

qa_postmetas (postid, title, content) 

Select all Open in new window

- 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) 

Select all Open in new window

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)
) )

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

Hello Dany:

Thanks ...

Yes you are correct table 2 has

qa_posts (postid, type, categoryid, userid, created, title, content, tags)

Did u try my scripts?

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' 

Select all Open in new window

This error is in second insert

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?

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)

Select all Open in new window

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)

Select all Open in new window

can you provide us with some sample data that you already have? and what you would like to have inserted?

how can I share the database dump securely ? I can send you dropbox link on your email...

send you a message ... you should have it