/    Sign up×
Community /Pin to ProfileBookmark

How do you select users, then update from result in one query?

Copy linkTweet thisAlerts:
Mar 03.2005

How do you check the database where column_row = “1” then update all users that resulted in that search to “2”?

to post a comment
PHP

11 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogMar 03.2005 — <i>
</i>UPDATE table_name SET col_name = 2 WHERE col_name = 1;
Copy linkTweet thisAlerts:
@d_brandusaauthorMar 03.2005 — thanks for responding, I understand how to update, I'm having a problem fetching array count, then UPDATE.

What I mean is, I want the query to count top 10 results, then update those 10 results it found.
Copy linkTweet thisAlerts:
@NogDogMar 03.2005 — <i>
</i>UPDATE table_name SET col_name = 2 WHERE
user_name IN(SELECT user_name FROM table_name WHERE col_name = 1 LIMIT 10);
Copy linkTweet thisAlerts:
@d_brandusaauthorMar 03.2005 — Okay, I can't get it to work, let me see if this will help better:

$referral_requirement = "10";

I want a query to to check total count with users that have both column specs. "Product_Code" = "001" AND "Status" = "new_user" count.

Say the database counts 10.

[code=php]
if($referral_requirement == $database_row_count) {

$update_new_status = mysql_query("UPDATE user_system SET List_Status = 'completed' WHERE Product_Code = 001 AND Status = new_user", $connection);

} else {

}
[/code]

I don't know how to count the rows, and put the amount it finds into: $database_row_count
Copy linkTweet thisAlerts:
@NutterMar 04.2005 — What about [url=http://us2.php.net/mysql_affected_rows]mysql_affected_rows()[/url]? I've never used it, but it appears to do what you're asking.
Copy linkTweet thisAlerts:
@NogDogMar 04.2005 — [url=http://www.php.net/mysql-num-rows]mysql_num_rows()[/url] can be used to get number of rows returned by a query.
Copy linkTweet thisAlerts:
@NutterMar 04.2005 — But, mysql_num_rows() only applies to SELECT queries, correct? I think, and may be wrong, that he's asking about how to tell how many rows are affected by an UPDATE query.

Just to make sure I'm reading your question right:

You're trying to run the "UPDATE..." query and want to know how many records are updated, correct?
Copy linkTweet thisAlerts:
@d_brandusaauthorMar 04.2005 — correct.
Copy linkTweet thisAlerts:
@NutterMar 04.2005 — What database server are you using, and what version? NogDog's second suggestion would only work in MySQL 4.1 or greater b/c of subquery support. I don't know about other DB servers.
Copy linkTweet thisAlerts:
@NutterMar 04.2005 — [i]Originally posted by d.brandusa [/i]

[B]correct. [/B][/QUOTE]

Did mysql_affected_rows() give you anything?
Copy linkTweet thisAlerts:
@NogDogMar 04.2005 — [i]Originally posted by Nutter [/i]

[B]Did mysql_affected_rows() give you anything? [/B][/QUOTE]
It should. Per the [url=http://us3.php.net/mysql-query]mysql_query() documentation[/url] (always a good place to start):
Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.[/quote]
×

Success!

Help @d_brandusa spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.27,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...