SQL - UPDATE with value from SELECT

Asked

Viewed 105 times

0

Dear, I have two tables (A and B), in which B has a foreign key of A. Example:

A(id, campo1, campo2)
B(id, idA, campo3, campo4)

I need to count the amount of B.idA and thereby define a true or false to update the campo2 of A. I thought of two ways (but none of them worked):

UPDATE A INNER JOIN B ON A.id = B.idA SET A.campo2 = IIF(Count(B.idA) = 1, FALSE, TRUE);

and

UPDATE A SET A.campo2 = (SELECT IIF(Count(*) = 1, FALSE, TRUE) AS Nome FROM B WHERE B.idA >= 1 GROUP BY B.idA);

I don’t know if every BDS has that clause IIF, I’m using Msaccess. She’s like a ternary parole. What’s the right way to do this UPDATE?

sql select sql-update
  • 1

    The first way, I can’t even say if it exists, the second would be as close as possible, if you want something similar to an IIF in the query, try to use the case when.

  • Actually, the first way does not work... I managed to solve by simplifying the query. I will post the answer.

1 answer

1


I managed to solve by simplifying a little and changing the logic:

UPDATE A SET campo2 = True WHERE ((A.id) In (SELECT B.idA FROM B GROUP BY B.idA HAVING COUNT(*) > 1));

I needed to count the B.idA to know which ones B.idA are repeated and with that set a field boolean for true in A. And I did it by placing a restriction WHERE in the UPDATE, and the restriction are the B.id repeated. With this we no longer needed to use the IIF. As there are several B.idA and I’ll update the entire table A at once, it was necessary to use the operator In, group the B.idA and put the COUNT(*) in the clause HAVING. Worked.

Browser other questions tagged sql select sql-update

You are not signed in. Login or sign up in order to post.