CODEWITHSUNDEEP
sqlsqlite
AFoglia
AFoglia

Reputation: 8128

Updating rows based on a multiple column condition

I'm trying to update some rows in a table based on two other columns in another table. As a toy model, consider two tables: People, with columns first_name, last_name, and has_license; Drivers, with columns first_name and last_name. Now I want to update the first table so has_license='Y' for all tuples of first_name and last_name that are also in the Drivers table.

I could do:

UPDATE people SET has_license='Y'
WHERE first_name + last_name IN (SELECT first_name + last_name FROM drivers)

(In my actual query, first_name and last_name are an externally-set record id and a date, and the subquery is more complex involving a join/EXCEPT clause.)

That's clumsy and has possible errors depending on the values. Ideally I could just make the tuple in sql like so:

UPDATE people SET has_license='Y'
WHERE (first_name, last_name) IN (SELECT first_name, last_name FROM drivers)

But that's invalid SQL (according to SQLite). So is what I want even possible?

(One other problem is that none of the tables have any primary keys, especially not single-column ones. If that weren't the case, I would use that to simply identify the row.)

Upvotes: 0

Views: 3372

Answers (2)

JNK
JNK

Reputation: 65157

This is SQL

Just use a JOIN!

UPDATE people 
SET has_license='Y'
FROM People
INNER JOIN Drivers
    ON Drivers.First_name = people.first_name
    AND Drivers.Last_name = people.last_name

In SQL Server I would just use aliases but I'm not familiar with the intricacies of SQLite syntax. This should be valid AFAIK.

EDIT

Below version uses EXISTS:

UPDATE people 
SET has_license='Y'
WHERE EXISTS (SELECT 1 FROM Drivers
              WHERE Drivers.First_name = people.first_name
              AND Drivers.Last_name = people.last_name)

Upvotes: 2

Steve Wellens
Steve Wellens

Reputation: 20620

I think you can concatenate the fields:

...first_name + last_name) IN (SELECT first_name + last_name...

Or

...first_name  + ' : ' +  last_name) IN (SELECT first_name  + ' : ' +  last_name...

Upvotes: 0

Related Questions