SQL Update

[stephen]

I have 2 tables that are joined together by a field called user_id

If I bring up 2 fields from both tables in text boxes, what is the SQL syntex to update the two records.

I thought it would be

UPDATE table1,table2 SET field='$field',etc WHERE table1.user_id=table2.user_id

but its not working
any comments?

thanks

[smarlowe]

Trying to make an update or insert work across multiple tables can quickly result in non-SQL92 compliant stuff that you can't transport easily if need be.

Just write it as two inserts/updates. If you're using a real database, then bracket them in a begin/commit pair and the performance difference between one and two inserts is unmeasurable.

If you're in MySQL, and you don't need the transactions for integrity reasons, it'll run just fine.

[stephen]

ok, so if I write two seperate statments it should work.

The problem is a variable is set for user_name which is in table1.

$sql="UPDATE table1 SET field1='$field1' WHERE user_name='$user_name'";
$sql="UPDATE table2 SET field2='$field2' WHERE table2.user_id=table1.user_id";

How can the second statment work if I am only selecting one table.

[smarlowe]

What database are you using? If it supports subselects you could do it like this:

(second query🙂

update table2 set field='$field' where user_id=(select id from table1 where username='$user_name');

would work, otherwise you might have to do a select and build another query for mysql.