SQL Update table field x where field y is z

Question

How can I insert values into a SQL Server table, into column X, where column Y is Z?

INSERT INTO dbo.Cities (County_Id) 
WHERE Code >= 10000 AND Code < 20000
VALUE 20

How do the Cities table and the Counties table relate to each other? — Thomas, May 22 '12 at 16:47
A bit odd that `Cities.County_Id` maps to `Counties.Name` but ok. — Thomas, May 22 '12 at 16:51
I need to give all cities that have Code between 10000 and 20000, County_Id of for example 20 — Davor Zubak, May 22 '12 at 16:55
Hrmm so you want to insert the Name from County into the County_Id of Cities? Or did you mean the Id of County? — Eric H, May 22 '12 at 17:03

score 2 · Answer 1 · edited May 23 '17 at 12:11

2

First of all, it sounds like you are trying to do an UPDATE, as INSERT is used to add a whole new record in the table as opposed to updating one or more existing record(s).

You really should be using UPDATE-JOIN instead of INSERT-SELECT, if I understand your requirement correctly. This StackOverflow thread provides a good example/explanation.

edited May 23 '17 at 12:11

Community

1
1

answered May 22 '12 at 17:01

Web User

6,996
13
57
86

score 2 · Accepted Answer · answered May 22 '12 at 17:03

2

UPDATE Cities SET County_Id = 20 WHERE Cities.Code >= 10000 AND Cities.Code < 20000

answered May 22 '12 at 17:03

Eric H

1,661
1
11
14

score 1 · Answer 3 · answered May 22 '12 at 16:50

Insert dbo.Cities( County_Id )
Select Name
From dbo.Counties
Where Counties.Id = 20
    And Exists  (
                Select 1
                From dbo.Cities As C1
                Where C1.County_Id = Counties.Name
                    And C1.Code >= 10000
                    And C1.Code < 20000
                )

SQL Update table field x where field y is z

3 Answers3