updating with inner join help

[sudani1969]

hi
i have 2 table t1 and t2 i would like to update t1 with value from t2 in t1 i have cloumn called log_out i would like to update that column with value according to 2 criteira from user table user ID and date the user ID is enterd though text box
here is code done in SqlYog :

UPDATE `attendance`.`login_details`
INNER JOIN `attendance`.`users`
ON (`login_details`.`user` = `users`.`id`)
SET login_details.logout = CURTIME()
WHERE users.username = 'abbas' AND login_details.date = CURDATE()
could you advice on code please :apologetic::apologetic:

 

[jmcilhinney]

There's no such thing as updating with an inner join. Joins are for retrieving data only. I suggest that you consult the SQL documentation for your database to see the actual syntax for an UPDATE statement looks like. Once you understand that you can look at how to use it to do what you want to do. If you want to update one table with data from another then that is going to involve at least one SELECT statement and one UPDATE statement.

 

[sudani1969]

thanks you for taking the time to answer my question
i was trying to update a value in a table where the user is = '?????' that user is fetched from a user table then updated the value in login table against that user
that is what i was trying to do and it worked that is why i used the INNER JOIN it didn't work at first because i had a syntax error near users.username = '"&txtUser.text &"'
but it is working fine now thanks

 

[dimpledevani]

I am sorry to say but there does not exist any such joins for insertion or updation
you need to have two update queries for both tables

 

[cjard]

Well now, that's not strictly true.. In oracle we can say:

UPDATE
(SELECT * FROM person INNER JOIN address ON person.addressID = address.ID)
SET name = address1, address2 = age

though it might not be sensible to overwrite a person's name with their address or their address with their age, it's doable. It's important to note that oracle will only allow rows to be updated if it is sure that the row hasnt been duplicated as a result of a join. Suppose person.addressID was NOT UNIQUE, it means 2 people can have the same address, so one address row might end up associated with 2 people and thus that 1 row cannot simultaneously have its address2 updated to the individual ages of both people.. The update the other way is fine; 2 unique people can have their names overwritten with their shared adddress line
Suffice to say, Oracle will only allow simultaneous update of tables that are 1:1 related, but it will allow update of the M table in an M:1 joined query

Hence UPDATE syntax in Oracle is "UPDATE (datablock) SET col=value" and it's not forced that datablock is a table

I've no idea if SQLserver can update 2 tables simultaneously, all the examples i've seen have an UPDATE written more like:
UPDATE t1
SET c1 = t2.c2
FROM t1 inner join t2 on t1.col=t2.col

Which implies microsoft choose to solve the problem another way, namely by limiting the update to one table only. I've no idea what happens if the join results in multiple same rows of t1 per unique rows of t2

In the case of MS Access.. I believe it's more flexible, but possibly more stupid; it will allow update of an M:1 join, and in either direction.. In the case of the first example, where we're setting address line 2 to be the person's age, where there are 2 matching people, access just leaves the address line updated to the one that happened last in the natural join order, thus it's anyone's guess whether addressline2 contains person 1's age of 27 or person 2's age of 31.. It's not predictable, and rerunning the same query again another day may give a different result. I could be wrong about Access entirely, it's been many years since i wrote an access query that updates one table with values from another


Ultimately, we get to a situation where the only way to update 2 tables sensibly is if they are 1:1 related and then theres an argument that all the data should be in the same table anyway.. But it's not exactly correct to say that updates cannot be made to joins

 

[dimpledevani]

hey thank you for this update actually i am just a beginner and have used only sql server till now and according to it joins cannot be made the way you showed but its interesting about oracle thanks again....

 

[cjard]

have used only sql server till now and according to it joins cannot be made the way you showed

I dont use SQLS myself, I just got my info from Microsoft's MSDN article about UPDATE queries