• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

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

0
CalmSoul
Asked:
CalmSoul
  • 10
  • 8
1 Solution
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now