'IF' statement in SQL?

[matpj]

Hi all,

I am updating a table from another, using a seperate script for each field.
One of the fields I update is always in the source table but only sometimes in the destination table.

Is it possible to write some SQL that checks for a value in the target table and if it exists update the corresponding data, if it doesn't exist create the row with that value and then update the corresponding data?

The first table consists of a row for each Project with other columns including 'Project Manager', 'Product', 'Client' etc

the second table has ROWS for each of the attributes above.
Each Project may have several rows, with each row being either 'Project Manager', 'Product', 'Client' Etc.

Sorry if I am not explaining myself very well.
(for those who use Microsoft Project the second table in my 'problem' is MSP_TEXT_FIELDS table.)

 

[dhookom]

Try enter a few records from each of your tables and the desired results. A question like this begs for an example.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[matpj]

ok, here is an example of the destination table (MSP_TEXT_FIELDS):

PROJ_ID REF_UID TEXT_FIELD_ID TEXT_VALUE
2186 0 188743731 CLIENT1
2186 0 188743734 UKE03646S
2186 0 188743740 AM
2186 0 188743743 1211
2186 0 188743999 R&D
2186 0 188744000 UKE03646 - Project1
2186 0 188744006 UKE03646
2186 0 188744007 Paul Martin
2186 0 188744008 Product xyx
2186 0 188744009 R&D
2186 0 188744011 12.1.0
2186 0 188744013 05 APR 04


as you can see there are several rows each with the same proj_id

each row has a text_value which corresponds to an individual field_id (as shown)
in this example, there is a date field which has TEXT_FIELD_ID of 188744013 and a TEXT_VALUE of 05 APR 04 (text string)

the source table (MSP_SYNCHTABLE) will contain one row for each project.
e.g.
PROJ_ID DATE CLIENT PRODUCT VERSION etc
2186 05 APR 04 CLIENT1 Productxyz 12.1.0

I use a lotus application to update the SYNCTABLE with project information from a Lotus database.
I then run a script to update the MSP_TEXT_FIELDS table with the information in MSP_SYNCHTABLE

the DATE column doesn't always exist in the MSP_TEXT_FIELDS table, so when I update it, I need to check it exists first.
Logically it would be:

LINK MSP_SYNCHTABLE with MSP_TEXT_FIELDS on PROJ_ID
IF MSP_TEXT_FIELDS.TEXT_FIELD_ID contains 188744013 (code for DATE) then UPDATE the TEXT_VALUE field in that row with MSP_SYNCHTABLE.DATE
ELSE INSERT ROW with all required data (i.e. proj_id = 2186, field_id 18744013 etc)

does that make more sense?

 

[PHV]

field_id 18744013
Where are the TEXT_FIELD_ID values coming from ?

If you have correct unique composite index you may first do an append query and then an update query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[matpj]

hi,

the field_id values come from another table.
basically we have som VB that displays a project defnition box in MSP.
this uses field values which are then translated using a conversion table into TEXT_FIELD_ID's you see above.

I don't want to run an append query unless a particular project doesn't already have a row in the MSP_TEXT_FIELDS table containing the TEXT_FIELD_ID mentioned above.
I can use an update query as I am with the rest of the fields:

UPDATE MSP_TEST_MSP_TEXT_FIELDS INNER JOIN MSP_TEST_MSP_SYNCHTABLE ON [MSP_TEST_MSP_SYNCHTABLE].[Proj_ID]=[MSP_TEST_MSP_TEXT_FIELDS].[Proj_ID] SET MSP_TEST_MSP_TEXT_FIELDS.TEXT_VALUE = [MSP_TEST_MSP_SYNCHTABLE]![CLIENT_CODE]
WHERE ((([MSP_TEST_MSP_TEXT_FIELDS]![TEXT_FIELD_ID])=188743731)) And [MSP_TEST_MSP_TEXT_FIELDS].[TEXT_REF_UID]=0;

but first I must make sure that all projects have a row as described.

is this understandable?

thanks for your help,
Matt

 

[PHV]

Take a look at the NOT EXISTS condition in a WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[matpj]

good thinking!
i'll try and come up with a statement.
Matt

 

[matpj]

PH,

i've been thinking about this and I still don't know how I can apply a NOT EXISTS in the solution.
here is a simplified example of my table:

PROJ_ID FIELD_ID VALUE
2186 188744016 Value1
2186 188743743 Value2
2186 187440013 Value3
2100 188744016 Value1
2100 188743743 Value2
3999 188744016 Value1

I would then need to run a script that will include the following two rows:

2100 187440013 Value3
3999 187440013 Value3

as these rows don't exist with those proj_id's

I don't know how to combine two fiels in a NOT EXISTS.

are you able to guide me?
thanks,
Matt

 

[PHV]

I don't know how to combine two fiels in a NOT EXISTS
WHERE NOT EXISTS (SELECT * FROM TableName WHERE fieldone=something And fieldtwo=anotherthing)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244