Change values while inserting table? 2

[briglass]

Hello,

Right now I have code that successfully inserts one table into another.

I would like to be able to change the values in one column on the final merged table.

strsql$ = "INSERT INTO table1 SELECT table2.* FROM table2;"
db.Execute strsql$

In the final table1, if the value under the column 'section' is "full", I would like it to be changed to "skip." If the value is "skip," I would like it to be changed to "full"... for the entire table1.

Is that possible?

Any ideas?




Thanks, (-:
Brian

 

[Golom]

Try
[blue][tt]
UPDATE Table1 SET

Section = IIF ( section = "FULL", "SKIP",
IIF ( section = "SKIP", "FULL",
section )
[/tt][/blue]

 

[briglass]

Thanks for the reply!

Two questions:

1) Is that all to be as one SQL statement?

2) Won't that change everything to "Full" since it will change everything to "Skip" and then all "Skip" to "Full"?



Thanks, (-:
Brian

 

[Golom]

Two answers

1. No. Run that after you do the insert. With the INSERT you are creating new records so there is no existing value to be changed. This runs after the insert to change the values that are now in the table.

2. No. Think of what happens with a single record. If the value is "FULL" then it's changes to "SKIP"; if it's "SKIP" then it's changed to "FULL". If it's neither one then it is unchanged. The "IIF"s are evaluated only once so it doesn't do a double change on the record before and again after the change.

This must be done this way. If you were to change all "FULL" to "SKIP" and then all "SKIP" to "FULL" then your table would contain no "SKIP" records (or conversely, no "FULL" records if you did it in the reverse order.)

 

[briglass]

Thanks again!

I see how it will work now. I'm a bit confused about the format, still. Should the SQL statement read:

sql$= "UPDATE Table1 SET Section = IIF ( section = "FULL", "SKIP", IIF ( section = "SKIP", "FULL", section )"

Thanks, (-:
Brian

 

[lespaul]

You may be confused because of the way Golom wrote the query:

Section = IIF ( section = "FULL", "SKIP", IIF ( section = "SKIP", "FULL", section ))

the access iif statement is constructed:

iif(condition, value if true, value if false)

in golom's SQL the first part is evaluated (is section = Full) if yes then change it to skip, if NO then check if section = Skip, then change it to full, if the second check if false then return the value of section.

Make a little more sense now?

leslie

 

[briglass]

lespaul-

Yes, thank you. What about if I had a third condition? I need all "screen" to be changed to "skip" as well.



Thanks, (-:
Brian

 

[Golom]

Leslie
Thanks for translating. I guess I don't format things the way that sane people do.

 

[Golom]

briglass
Just a simple extension of what we did before
[blue][tt]
UPDATE Table1 SET

Section = IIF ( section = "FULL" OR section = "SCREEN", "SKIP",
IIF ( section = "SKIP", "FULL",
section )
[/tt][/blue]

 

[briglass]

Ok, cool.

Now let me see if I have it right:

sql$= "Section = IIF ( section = "FULL" or section = "SCREEN", "SKIP", IIF ( section = "SKIP", "FULL", section ))

Is that right?

Thanks, (-:
Brian

 

[briglass]

wait, wait:

sql$ = "UPDATE Table1 SET Section = IIF ( section = "FULL" or section = "SCREEN", "SKIP", IIF ( section = "SKIP", "FULL", section ))"

Thanks, (-:
Brian

 

[Golom]

Almost.

You need the UPDATE table SET (using your table name) before what you have.
[blue][tt]
sql$= "UPDATE Table SET Section = IIF ( section = "FULL" or section = "SCREEN", "SKIP", IIF ( section = "SKIP", "FULL", section ))
[/tt][/blue]

 

[lespaul]

I wouldn't call myself SANE.....
but, for a second it was hard for me to see what you had done and I KNEW what you had done! Sometimes it just takes a little different perspective to help someone "see the light".

les

 

[briglass]

And these are all single quotes, right?

Thanks, (-:
Brian

 

[Golom]

Yes ... sorry ... single quotes except the ones at the beginning and end. If you were writing this in the query designer then either one would do but when assigning it to a string you want double quotes around the whole string and single quotes inside the string.

 

[briglass]

Oops...

I have one more question...

What if there are 2 cases where I don't want to switch this... and that case is if the value in the column "area" is "DIS0" or "DISX"...

Will this add much complexity?

Thanks, (-:
Brian

 

[Golom]

Just add a WHERE clause to your existing statement
[blue][tt]
WHERE Area NOT IN ('DIS0', 'DISX' )
[/tt][/blue]

 

[briglass]

Either way, I still get a "syntax error in the UDATE statement" ...


Any ideas?



Thanks, (-:
Brian

 

[briglass]

Even this gives me a syntax error:

"UPDATE log SET section = IIF (section = 'FULL', 'SKIP', 'FULL')

Thanks, (-:
Brian

 

[Golom]

Try this
[blue][tt]
sql$= "UPDATE Table1 SET [Section] = IIF ( [section] = 'FULL' or [section] = 'SCREEN', 'SKIP', IIF ( [section] = 'SKIP', 'FULL', [section] )) WHERE [Area] NOT IN ('DIS0', 'DISX' )"
[/tt][/blue]
I'm not certain but "section" and/or "area" may be reserved words.