sql commands help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql commands help

Author

Topic

demetris20
Starting Member

8 Posts

Posted - 2011-10-11 : 13:39:24

hello
i have a forum and i mess it up with the aql commands
i have 2 tables that have 3 rows
the 2 rows are the same and the third one its different
so i was wondering if i can copy the 3 row from the first table to the row of the other table by using the other 2 rows as a link

thanks
demetris20

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 13:46:38

very vague question. can you at least show sample of how data exists in both tables with some sample data and give what output you're expecting out of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

demetris20
Starting Member

8 Posts

Posted - 2011-10-11 : 13:59:17

ok here is some example
there are 2 tables one called posts and the other one called topics
both tables have the follow rows-column called the same
topic id and forum id
from the topics table the third rows its called topic_poster and from post table its called poster_id
i want the topic_poster to be insert into the poster id as all poster_id is 0 after i mess it up
so i was wondering if it can be done

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 14:17:14

[code]
insert posts (poster_id)
select distinct topic_poster
from topics
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

demetris20
Starting Member

8 Posts

Posted - 2011-10-11 : 14:47:23

i will try it and let you now but the actual tables are called phpbb_topics and phpbb_posts
do i replace it with that thanks

demetris20
Starting Member

8 Posts

Posted - 2011-10-11 : 16:42:37

SQL ERROR

Field 'post_text' doesn't have a default value [1364]

this is the result i got

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 01:37:56

what is value you want to populate for post_text
since you dont have default value specified you need to pass a value for it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-12 : 05:17:13

You can use merge statement feature of sql server 2008, have a google on it

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

demetris20
Starting Member

8 Posts

Posted - 2011-10-12 : 08:08:11

ok i will try and explain more
i have a php website and 72 sql tables
the 2 tables i want to compine are called phpbb_topics and phpbb_posts
the phpbb_topics have the following fields
topic_id
forum_id
icon_id
topic_title
topic_poster

and the phpbb_post the following
post_id
topic_id
forum_id
icon_id
poster_id
post_subject
post_title

topic_id,forum_id,icon_id have the same values row,type etc
the topic_poster and poster_id had the same as the other table but when i did something wrong the poster_id had nothing us a value
but because the topic_poster keep having the records from the poster id i want to copy those value and put them in the poster_id

so i am looking of an sql command to take the value the topic poster value and copy them in the place of the poster_id
i can try and make it one by one but will take ages as there are more than 10000 pages with 30 rows each
basically i am looking something like this
take from phpbb_topic where topic_id and forum_id=phpbb_posts topic_id and forum_id take the topic_poster from phpbb_topics and put it in the phpbb_post in where it is the poster_id

i hope this will make more sence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 08:35:04

[code]update pp
set pp.poster_id= pt.topic_poster
from phpbb_post pp
join phpbb_topics pt
on pt.topic_id = pp.topic_id
pt.forum_id = pp.forum_id
pt.icon_id = pp.icon_id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

demetris20
Starting Member

8 Posts

Posted - 2011-10-12 : 08:46:42

i believe the command its like this
UPDATE phpbb_posts SET phpbb_posts.poster_id = phpbb_topics.topic_poster
FROM phpbb_posts
JOIN phpbb_topics
ON phpbb_topics.topic_id=phpbb_posts.topic_id
phpbb_topics.forum_id=phpbb_posts.forum_id
phpbb_topics.icon_id=phpbb_posts.icon_id

if yes then i will post the result in 5 seconds

demetris20
Starting Member

8 Posts

Posted - 2011-10-12 : 08:49:28

SQL query:

UPDATE phpbb_posts SET phpbb_posts.poster_id = phpbb_topics.topic_poster FROM phpbb_posts JOIN phpbb_topics ON phpbb_topics.topic_id = phpbb_posts.topic_idphpbb_topics.forum_id = phpbb_posts.forum_idphpbb_topics.icon_id = phpbb_posts.icon_id

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM phpbb_posts
JOIN phpbb_topics
ON phpbb_topics.topic_id = phpbb_posts.top' at line 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 09:02:17

it should be


update pp
set pp.poster_id= pt.topic_poster
from phpbb_post pp
join phpbb_topics  pt
on pt.topic_id = pp.topic_id
and pt.forum_id = pp.forum_id
and pt.icon_id = pp.icon_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 09:03:38

One more thing. just noticed you're using mysql. this is ms sql server forum. so solutions given here are sql server specific. You can try given suggestion. In case its not working in MYSQL, please try your luck at www.dbforums.com. We dont have much expertise in MySQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

demetris20
Starting Member

8 Posts

Posted - 2011-10-12 : 09:07:07

yes you are right it didnt work
thanks for the advice though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources