taking values from one table value and applying it another?

[Malcolm]

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?

 

[Ghost]

Yeah, it's totally possible.

INSERT into table2 from table1, for all users

INSERT INTO table2 (columnName2) SELECT column1 FROM table1

INSERT into table2 from table1, for a single user

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

UPDATE table1 SET column2 = column1

for a single user

UPDATE table1 SET column2 = column1 WHERE userid = 3736

 

[Malcolm]

Yeah, it's totally possible.

INSERT into table2 from table1, for all users

INSERT INTO table2 (columnName2) SELECT column1 FROM table1

INSERT into table2 from table1, for a single user

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

UPDATE table1 SET column2 = column1

for a single user

UPDATE table1 SET column2 = column1 WHERE userid = 3736

I'm going to give this a try, thanks again 🙂

 

[Ghost]

You can also update table2 from table1 like this...

UPDATE table2 SET column2 = (SELECT column1 FROM table1 WHERE userid = 3736) WHERE userid=3736

 

[Malcolm]

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?

 

[Ghost]

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?

Yeah, you would do something like this... This will update the credits if the user is found in table2.

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.

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:

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:

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!

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.

 

[Malcolm]

I’m at work now but I’ll give this a try when I get home!

 

[Ghost]

I’m at work now but I’ll give this a try when I get home!

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

 

[Malcolm]

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

Awesome, I appreciate this a lot!

What does Where mean?

 

[Ghost]

Awesome, I appreciate this a lot!

What does Where mean?

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.

 

[Malcolm]

Wow amazing thanks @Ghost i appreciate you helping me out 🙂

 

[Ghost]

Wow amazing thanks @Ghost i appreciate you helping me out 🙂

No problem 🙂
let me know if you need anything else