• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

taking values from one table value and applying it another?

Malcolm

Malcolm

Administrator
Administrator
Staff Team
Code Plus
Company Plus
Hi there,

Is it possible to copy the content from a table column within database to another table column?

Also, wondering if you can set a condition where if user Id is 3736 then apply value to 3736?
 
Sort by date Sort by votes
Yeah, it's totally possible.

INSERT into table2 from table1, for all users
SQL: 
INSERT INTO table2 (columnName2) SELECT column1 FROM table1

INSERT into table2 from table1, for a single user
SQL: 
INSERT INTO table2 (columnName2, userid) SELECT column1, userid FROM table1 WHERE userid = 3736

UPDATE table1 column2 with the value from table1's column1, for all users
SQL: 
UPDATE table1 SET column2 = column1

for a single user
SQL: 
UPDATE table1 SET column2 = column1 WHERE userid = 3736
 
Upvote 0 Downvote
Ghost said:
Yeah, it's totally possible.

INSERT into table2 from table1, for all users
SQL: 
INSERT INTO table2 (columnName2) SELECT column1 FROM table1

INSERT into table2 from table1, for a single user
SQL: 
INSERT INTO table2 (columnName2, userid) SELECT column1, userid FROM table1 WHERE userid = 3736

UPDATE table1 column2 with the value from table1's column1, for all users
SQL: 
UPDATE table1 SET column2 = column1

for a single user
SQL: 
UPDATE table1 SET column2 = column1 WHERE userid = 3736
Click to expand...
I'm going to give this a try, thanks again :)
 
Upvote 0 Downvote
You can also update table2 from table1 like this...
SQL: 
UPDATE table2 SET column2 = (SELECT column1 FROM table1 WHERE userid = 3736) WHERE userid=3736
 
Upvote 0 Downvote
Just curious is possible to update a table based off a condition? So if user id on table 1 matches user id on table 2 update value credits. if that makes sense?
 
Upvote 0 Downvote
Malcolm said:
Just curious is possible to update a table based off a condition? So if user id on table 1 matches user id on table 2 update value credits. if that makes sense?
Click to expand...

Yeah, you would do something like this... This will update the credits if the user is found in table2.
SQL: 
UPDATE table2 SET credits = 1 WHERE userid IN (SELECT userid FROM table2) AND userid = 3637

You can set credits to whatever you want in the example above, or even do something like this...
This will add 1 credit for each row the userid is found in table1. So, for example +1 credit for each post of the user found in table1.
SQL: 
UPDATE table2 SET credits = credits + (SELECT count(id) FROM table1 WHERE userid = 3736) WHERE userid = 3736

This would add 3 credits for each post found in user_posts:
SQL: 
UPDATE user_credits SET credits = credits + (SELECT count(id) FROM user_posts WHERE userid = 3736)*3 WHERE userid = 3736

This would add 3 credits for each post found in user_post AND also +6 credits for each thread the user made:
SQL: 
UPDATE user_credits SET credits = credits + ((SELECT count(id) FROM user_posts WHERE userid = 3736)*3) + ((SELECT count(id) FROM user_threads WHERE userid= 3736)*6) WHERE userid = 3736

You could also create a column in user_posts and user_threads to mark whether credits have been given yet or not! That way you can run the query on a cron to award points for new threads / new posts, but not give points for old ones!
SQL: 
UPDATE user_credits SET credits = credits + ((SELECT count(id) FROM user_posts WHERE userid = 3736 AND credits_awarded = 0)*3) + ((SELECT count(id) FROM user_threads WHERE userid= 3736 AND credits_awarded = 0)*6) WHERE userid = 3736

The "credits_awarded" column would be a tinyint column & would be 0 for "no, not awarded yet" and 1 for "awarded credits already".

:) I hope this helps.
Loads is possible.
 
Upvote 0 Downvote
Malcolm said:
I’m at work now but I’ll give this a try when I get home!
Click to expand...
Yeah give it a go! You could also add more WHERE statements to award credits for specific forum sections instead of all forum sections
For example you could say...
WHERE userid = 1 AND credits_awarded = 0 AND forum_id !=5
That would exclude all posts in forum id 5 - example: forum games section
 
Upvote 0 Downvote
Ghost said:
Yeah give it a go! You could also add more WHERE statements to award credits for specific forum sections instead of all forum sections
For example you could say...
WHERE userid = 1 AND credits_awarded = 0 AND forum_id !=5
That would exclude all posts in forum id 5 - example: forum games section
Click to expand...
Awesome, I appreciate this a lot!

What does Where mean?
 
Upvote 0 Downvote
Malcolm said:
Awesome, I appreciate this a lot!

What does Where mean?
Click to expand...

It tells the database what you want
You're basically saying "I only want data where the following is true..."

You can do things like
SELECT userid FROM users WHERE email = "[email protected]"

or you could do...
SELECT userid FROM users WHERE email LIKE '%@gmail.com'
...to find all the users using gmail

SELECT userid FROM users WHERE post_count > 1000
To find users with over 1,000 posts if post count is a column in the users table

You can also use order by / limit to filter results a bit...
SELECT userid FROM users ORDER BY sign_up_date DESC LIMIT 100
... to find the first 100 members

WHERE lets us specify what results we want. If you don't use a WHERE, the SELECT statement will return all of the rows from the database.
You can also do things like this...
SELECT postID FROM forum_posts WHERE category_id = 5 ORDER BY post_date DESC LIMIT 100
The 'select' part of this gets the post IDs
The 'order by desc' tells the database to order them by most recent (descending post date order)- not in order of first post to last
The 'limit 100' tells the database to return the first 100 results
Because of the order by & limit additions, the database shows us the most recent 100 posts
The WHERE category_id = 5 part of this tells the database we only want posts in category 5, so we get cat 5's most recent 100 posts with this query.

There's so much you can do with SQL.
 
Upvote 0 Downvote
Top Bottom