INSERT WHERE NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

INSERT WHERE NULL

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-04-25 : 10:02:00

I'm trying to write an SQL insert statement to insert a value into one of four columns, whichever is null. The record to insert to is chosen by a drop down list in HTML.

"INSERT INTO TEAMS (NAME1 OR NAME2 OR NAME3 OR NAME4) VALUES ('FIRST_NAME','LAST_NAME') WHERE COURSE = '" & courseValue & "'"

Obviously not a working SQL statement, but I'm just confused as to where to start.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-25 : 10:17:35

You could use a CASE statement in your query:

 
INSERT INTO TEAMS (field1, field2, field3, field4)
VALUES (
CASE <condition> WHEN <true> THEN <value1> ELSE <value2> END,
   CASE <condition1> WHEN <true> THEN <value1> ELSE NULL END,
   CASE <condition2> WHEN <true> THEN <value2> ELSE NULL END,
   CASE <condition3> WHEN <true> THEN <value3> ELSE NULL END,
       )
WHERE COURSE = <value>

This would probably work in your situation

Dorffius
Starting Member

36 Posts

Posted - 2002-04-25 : 10:24:02

I'm not sure if I understand completely. I don't want to test the values with the case statement, but the columns that I want to insert into. I want to be able to look at column 1 and if there is no name there, insert one. If there is a name there move to column 2, check, insert if null, if not, move on, and so on.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-25 : 10:34:47

Hi

You need to use some Dynamic SQL. Do a search on SQLTeam and you might find some articles written by some crackhead that might give you some ideas.

Damian

eddie
Starting Member

45 Posts

Posted - 2002-04-25 : 10:41:55

so, if field1 is null, what do you insert? Firstname, lastname, both?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-25 : 10:42:04

You're not using the correct terminology either, and maybe that's why this is troubling you. You don't INSERT values into a column. An INSERT puts an entire row into a table, and each column you specify has a value. If you don't specify a column, the default value will be used.

If you want to change columns in an existing row, you need to use UPDATE.

eddie
Starting Member

45 Posts

Posted - 2002-04-25 : 10:57:21

right so you would need a unique identifier for the row and then wouldn't it be as easy as..

update table1
set field1=
CASE when field1 is null then name
ELSE NULL
END,
field2=
CASE when field is null then name
ELSE Null
END, etc...

where id = id for the row that you want updated...

Edited by - Eddie on 04/25/2002 11:16:57

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-25 : 10:58:10

OMG

Stupid me. I didn't realise you were trying to use an INSERT with a WHERE clause.

You can not do that. You insert a row, not a "field", this isn't Excel.

I shouldn't drink Jack Daniels and post at the same time.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources