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)



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

Open in new window

LVL 5
CalmSoulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dany BalianCTOCommented:
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) ;
0
CalmSoulAuthor Commented:
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?
0
Ray PaseurCommented:
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.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

CalmSoulAuthor Commented:
I have 3 tables

TABLE 1 COLUMNS:

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

Open in new window


TABLE 2 COLUMNS:

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

Open in new window


TABLE 3 COLUMNS:

qa_postmetas (postid, title, content) 

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) 

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)
) )
0
Dany BalianCTOCommented:
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
0
Dany BalianCTOCommented:
try this!
//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)

Open in new window

you don't need an if exists syntax.. because the insert will insert everything that is missing only
however it would be better if you have a foreign key between test1 and qa_posts
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CalmSoulAuthor Commented:
Hello Dany:

Thanks ...

Yes you are correct table 2 has

qa_posts (postid, type, categoryid, userid, created, title, content, tags)
0
Dany BalianCTOCommented:
Did u try my scripts?
0
CalmSoulAuthor Commented:
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' 

Open in new window

0
CalmSoulAuthor Commented:
This error is in second insert
0
CalmSoulAuthor Commented:
can we add skip if entry exist?
0
Dany BalianCTOCommented:
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?
0
CalmSoulAuthor Commented:
yes it the url which is the primary key
0
Dany BalianCTOCommented:
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)

Open in new window

0
CalmSoulAuthor Commented:
same issue - #1062 - Duplicate entry '906-qa_q_extra' for key 'PRIMARY'
0
Dany BalianCTOCommented:
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)

Open in new window

0
Dany BalianCTOCommented:
can you provide us with some sample data that you already have? and what you would like to have inserted?
0
CalmSoulAuthor Commented:
how can I share the database dump securely ? I can send you dropbox link on your email...
0
CalmSoulAuthor Commented:
send you a message ... you should have it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.