CODEWITHSUNDEEP
mysql
Sohel Mansuri
Sohel Mansuri

Reputation: 564

Mysql UPDATE query with two tables

I wanted to run this query:

UPDATE up SET up.pts = uc.checkin_worth WHERE uc.email = up.email AND uc.company_id = up.company_id AND uc.email = '[email protected]' AND uc.company_id = '4' AND uc.qrcode = 'j'

However, I am getting an error because I don't know how to combine two tables (uc and up) in a UPDATE query.

Can anyone help me solve this?

Thanks,

Upvotes: 0

Views: 70

Answers (2)

Danilo
Danilo

Reputation: 2036

Try with this:

UPDATE up, uc 
SET up.pts = uc.checkin_worth, 
WHERE uc.email = up.email AND uc.company_id = up.company_id AND uc.email = '[email protected]' AND uc.company_id = '4' AND uc.qrcode = 'j'

Upvotes: 1

AndreKR
AndreKR

Reputation: 33678

Just use the normal JOIN syntax:

UPDATE up JOIN uc ON uc.email = up.email AND uc.company_id = up.company_id
SET up.pts = uc.checkin_worth
WHERE uc.email = '[email protected]' AND uc.company_id = '4' AND uc.qrcode = 'j'

You can also use the old comma syntax, which is more similar to your original query:

UPDATE uc, up
SET up.pts = uc.checkin_worth
WHERE uc.email = up.email
AND uc.company_id = up.company_id
AND uc.email = '[email protected]'
AND uc.company_id = '4'
AND uc.qrcode = 'j'

Upvotes: 2

Related Questions