0

hi here i have two tables named as test and test2.

test is as follows

enter image description here

test2 is as follows

enter image description here

i am using below sql code to get the below output. 

UPDATE `test`
SET `availability` = 'ok'
WHERE
    `id` = '(SELECT
test.id
FROM
test2
INNER JOIN test ON test.id = test2.PId)';

I requires below output. but it outcomes no any output. kindly help. any mistakes done by my end or if there is any best mothod to get below output, kindly mention

enter image description here

Chamath Gunasekara
  • 129
  • 1
  • 2
  • 11

3 Answers3

1

I think you're looking for something along the lines of

UPDATE test 
INNER JOIN test2 on test.id = test2.PId
SET test.availability = 'OK'
NcDreamy
  • 785
  • 6
  • 14
0

Sounds like you don't need an update, just a query with a join:

SELECT test.id, test.name, CASE WHEN teste2.pid IS NOT NULL THEN 'OK' END
FROM   test
JOIN   test2 ON test.id = test2.pid
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • hi i jut need to update the value to the 'availability' column is that possible, i hope i should use an update query to enter ok value for the availability column. – Chamath Gunasekara Aug 10 '17 at 04:48
0

I am not sure if this will work.but hey we all try here right..

UPDATE `test`
SET `availability` = 'ok'
WHERE
`id` in '(SELECT
PId from test2)';

I don't get , why do you need inner join.You only need those id's that are present in test2 table,then just take it. although i have used 'in' keyword,try with '=' also but i doubt it will work because inner query is returning a list of id's.thanks.

vic
  • 29
  • 9