mysql_query("INSERT INTO withdraw (username,amount,date) SELECT username,amount,NOW() FROM accounts WHERE username='$username' UPDATE accounts SET amount =0 WHERE username = '$user'")
i want to update data after insert but this query not working
Just put all the columns you need to insert in the select-part
INSERT INTO withdraw (username, amount, some_date)
SELECT username, amount, now()
FROM accounts
WHERE username='$username';
This is how the syntax should be -
INSERT INTO Table2
(client_last_name, client_first_name, taskDescription, category)
(SELECT clientLastName, clientFirstName, incidentDescription, category
FROM Table1
WHERE info_field IS NOT NULL)
ADVICE: Avoid using mysql_* functions as they are deprecated in recent PHP versions. Learn mysqli prepared statement or PDO and start implementing.
In my opinion, you can split your statement into two parts. Assuming that you want to make NOW() to be current date in all inserted rows (I write as in SQL Server because I can't write in MySQL, if you can please try to convert it into MySQL):
@date=NOW();
INSERT INTO withdraw (username,amount) SELECT username,amount FROM account WHERE
username=@username;
UPDATE withdraw
SET date=@DATE where ID=@@IDENTITY
If your table has IDENTITY ID column. In MySQL it is auto increment and an appropriate function is SCOPE_IDENTITY() (selects last inserted ID in MySQL, you can google for more information)