CODEWITHSUNDEEP
sqlsql-server
Mika Azulay
Mika Azulay

Reputation: 1

Update a column in a table to 2 different values

I need to update a column in a table with 2 different values by gender but in 1 query (only one update). I have a table with employees with genders male and females like this:

CREATE TABLE tb_Employee
(
  FNAME nvarchar(50) not NULL,
  LNAME nvarchar(50) not NULL,
  ID int not NULL,
  BDATE date not NULL,
  ADD_STREET nvarchar(50) NULL,
  ADD_NUM nvarchar(50) NULL,
  ADD_CITY nvarchar(50) NULL,
  Gender nvarchar(50) not NULL,
  salary dec(8,2) DEFAULT 0.0,
  PRIMARY KEY(ID),
  constraint Employee_Gender_value check (Gender in('Male','Female'))
)    

insert into tb_Employee values 
('Tom','Cohen',1,'01/2/1990','AA','11','LONDON','Male',4000),
('Pam','Jackson',2,'02/3/1989','BB','11','Delhi','Female',3000),
('John','Labor',3,'03/4/1988','CC','11','Delhi','Male',3500),
('Sam','Kruz',4,'04/5/1987','DD','11','New York','Male',4500),
('Todd','Pit',5,'01/2/1986','EE','11','Delhi','Male',2800),
('Ben','Jonson',6,'01/2/1985','FF','11','LONDON','Male',7000),
('Sara','Parker',7,'01/2/1984','GG','11','LONDON','Male',4800),
('Valiarie','Russell',8,'01/2/1983','HH','11','LONDON','Male',5500),
('James','Ten',9,'01/2/1980','AA','II','New York','Female',6500),
('Russell','Ted',10,'01/2/1978','AA','JJ','New York','Female',8800)

also I have a function that goes like this: (it gives me a table with employees with only the gender I sent)

create function getEmployeeByGender(@gender nvarchar(50))
returns table
as
return (select * from tb_Employee where Gender=@gender)

but I can't get it to update in 1 query

update dbo.getEmployeeByGender('Female')
set ADD_STREET = 'bbb'
update dbo.getEmployeeByGender('Male')
set ADD_STREET = 'aaa'

Upvotes: 0

Views: 59

Answers (3)

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

You can use the following UPDATE without functions (just a simple UPDATE with WHERE):

UPDATE tb_Employee 
    SET ADD_STREET = CASE WHEN Gender = 'Male' THEN 'aaa' ELSE 'bbb' END
WHERE Gender IN ('Male', 'Female')

demo on dbfiddle.uk

In case your really need to use the function you can use a INNER JOIN:

UPDATE tb_employee SET ADD_STREET = c.ADD_STREET
FROM tb_employee e INNER JOIN (
    SELECT ID, 'aaa' AS ADD_STREET FROM getEmployeeByGender('Male')
    UNION ALL   
    SELECT ID, 'bbb' FROM getEmployeeByGender('Female')
) c ON e.ID = c.ID;

demo on dbfiddle.uk

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Your function way over-complicates this situation. But let me assume that your actual problem is not quite so simple. After all, the simple update is the best approach.

I would recommend left join for this:

update dbo.tb_Employee
    set ADD_STREET = (cse when f.id is not null then 'bbb' else 'aaa')
    from dbo.tb_Employee e left join
         dbo.getEmployeeByGender('Female') f
         on f.id = e.id left join
         dbo.getEmployeeByGender('Male') m
         on m.id = e.id left join
    where m.id is not null and f.id is not  null

Upvotes: 0

Dale K
Dale K

Reputation: 27472

The only way you can use a function as part of the update is as follows - but there is no benefit from doing so.

update dbo.tb_Employee
  set ADD_STREET = 'bbb'
where id in (select id from dbo.getEmployeeByGender('Female'))

update dbo.tb_Employee
  set ADD_STREET = 'aaa'
where id in (select id from dbo.getEmployeeByGender('Male'))

or the following, but again not beneficial (at least not the way the question is worded)

update dbo.tb_Employee
  set ADD_STREET = case when id in (select id from dbo.getEmployeeByGender('Male')) then 'aaa' else 'bbb' end

Upvotes: 0

Related Questions