Avatar of CalmSoul
CalmSoul
Flag for United States of America 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)



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

MySQL ServerPHP

Avatar of undefined
Last Comment
CalmSoul

8/22/2022 - Mon
Dany Balian

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) ;
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?
Ray Paseur

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
CalmSoul

ASKER
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)
) )
Dany Balian

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
ASKER CERTIFIED SOLUTION
Dany Balian

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
CalmSoul

ASKER
Hello Dany:

Thanks ...

Yes you are correct table 2 has

qa_posts (postid, type, categoryid, userid, created, title, content, tags)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dany Balian

Did u try my scripts?
CalmSoul

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' 

Open in new window

CalmSoul

ASKER
This error is in second insert
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
CalmSoul

ASKER
can we add skip if entry exist?
Dany Balian

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?
CalmSoul

ASKER
yes it the url which is the primary key
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dany Balian

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

CalmSoul

ASKER
same issue - #1062 - Duplicate entry '906-qa_q_extra' for key 'PRIMARY'
Dany Balian

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Dany Balian

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

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

ASKER
send you a message ... you should have it
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.