Is there a way to search one table for certain values, and if successfully found, then update a diff-CodePudding

Good afternoon all,

This is my first time using SQL, so forgive me for being new. I have three different tables that I've created:

CREATE TABLE employee
(ID int,
fname text,
lname text,
age int,
hiredate date);

CREATE TABLE address
(ID int,
address1 text,
address2 text,
city text,
state text,
zip text);

CREATE TABLE contact
(ID int,
cellphone text,
homephone text,
email text);

I have instructions that I'm supposed to update a person's cellphone number in the contact table by "using fname and lname in where clause".

I know how to directly update the person's phone number in the table, but using an IN and WHERE in conjunction with UPDATE between two different tables is just really stumping me.. is there some way to do this that doesn't involve any kind of elaborate solution?

My line of thinking involved coming up with some kind of if-then solution. i.e. IF I can search table employee for a specific fname and lname and find it, THEN update the contact cellphone number with the new number I designate. But.. I have no idea if I can even do if-then statements like that. Anyone have any suggestions?

Thank you for your time!

CodePudding user response：

This uses an IN CLAUSE, because there could be more than one ID with the same name combination.

UPDATE contact
SET cellphone  = '1234567890'
WHERE ID IN ( SELECT ID FROM employee WHERE fname  = 'A' AND lname = 'B');

CodePudding user response：

update contact
set cellphone = 'xxx-xxx-xxxx', homephone = 'xxx-xxx-xxxx'
where ID = (
    select ID from employee
    where fname = 'Bob' and lname = 'Jones'
);

This does assume only one employee will match, otherwise it will cause an error (which is quite often what you want to happen.)